具有多个表的复杂INNER JOIN查询的回显结果

问题描述

这是我的第一个问题.我有一个复杂的SQL数据库，需要连接具有相同列名的不同表.

This is my first question here. I have a complicated SQL database and I need to join different tables which have the same column names.

赛事"是一项体育比赛.它包含链接到Tournament_stage表的Tournament_stageFK，包含与Tournament表链接的TournamentFK，包含与Tournament_Template表链接的Tournament_templateFK，包含与SportTable链接的sportFK.

"event" is a sports match. It contains tournament_stageFK which is linked to tournament_stage table, which contains tournamentFK which is linked to tournament table, which contains tournament_templateFK which is linked to tournament_template table, which contains sportFK which is linked to sport table.

因此，为了找出比赛的来源，我需要进行内部联接，否则我将不得不打开数据库数百万次.唯一的方法就是这样做，但是我不知道如何显示结果.我对结果的回应很糟糕，如下所示:

So in order to find out what sport the match is from, I need to do an inner join, otherwise I'd have to open the database millions of times. The only way to do it is this, but I don't know how to display the results. My poor attempt to echo the results is below:

$SQL = "SELECT sport.name,
country.name,
tournament_template.name,
tournament.name,
tournament_stage.name,
event.*
FROM tournament_template
INNER JOIN sport
ON tournament_template.sportFK = sport.id
INNER JOIN tournament ON tournament.tournament_templateFK = tournament_template.id
INNER JOIN tournament_stage ON tournament_stage.tournamentFK = tournament.id
INNER JOIN event ON event.tournament_stageFK = tournament_stage.id
INNER JOIN country ON tournament_stage.countryFK = country.id
WHERE DATE(event.startdate) = CURRENT_DATE()
ORDER BY sport.name ASC,
country.name ASC,
tournament_stage.name ASC,
event.startdate ASC";

$result = mysql_query($SQL);

while($get=mysql_fetch_array($result))
{
echo $result['event.name'];
echo "<br>";
}

推荐答案

您需要使用列别名并在访存调用中访问它们.代替 event.* ，明确说明所需的列:

You need to use column aliases and access those in your fetch call. Instead of event.*, be explicit about the columns you need:

$SQL = "SELECT sport.name AS sportname,
country.name AS countryname,
tournament_template.name AS templatename,
tournament.name AS tournamentname,
tournament_stage.name AS stagename,
/* Use explicit column names instead of event.* */
event.name AS eventname,
event.someothercol AS someother
FROM tournament_template
...etc...
...etc...";

// Later...
while($row=mysql_fetch_array($result))
{
  echo $row['eventname'];
  echo "<br>";
}

这篇关于具有多个表的复杂INNER JOIN查询的回显结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！