MySQL 如何LEFT JOIN具有相同列名的两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8119515/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to LEFT JOIN two tables with the same column name
提问by gordyr
I want to LEFT JOIN two tables with the same column name. I have two tables which I am trying to join but I keep getting an error:
我想 LEFT JOIN 具有相同列名的两个表。我有两个我正在尝试加入的表，但我一直收到错误消息：
column 'id' is ambiguous
returned in my JSON output.
在我的 JSON 输出中返回。
My current query:
我目前的查询：
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
LEFT JOIN
$sTable2
ON ($sTable2.id = $sTable.id)
$sWhere
$sOrder
$sLimit
";
Produces that error. How can I join these two tables as the join point when there is the same column name in each table?
产生那个错误。当每个表中的列名相同时，如何将这两个表作为连接点连接？
回答by knittl
Be explicit about which table the column belongs to. This also applies to the SELECT part of the query:
明确列属于哪个表。这也适用于查询的 SELECT 部分：
SELECT table1.column AS column1, table2.column AS column2
FROM table1
LEFT JOIN table2
ON table1.column = table2.column
To save you some typing time, use table aliases:
为了节省一些打字时间，请使用表别名：
SELECT t1.column AS column1, t2.column AS column2
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON t1.column = t2.column
回答by Rémi
The ambiguity is probably in the select, not in the join. The join looks OK. $aColumns probably contains "id" without table name specification or something.
歧义可能是在选择中，而不是在连接中。连接看起来没问题。$aColumns 可能包含没有表名规范或其他内容的“id”。
回答by Zohaib
The LEFT JOIN keyword returns all rows from the left table (table_name1),
even if there are no matches in the right table (table_name2).
In your case, both table1 and table 2 are same. So There might be no benefit of doing Left Join because ultimately all the rows will be returned. You might wanna use Inner Join.
在您的情况下，表 1 和表 2 都相同。因此，执行 Left Join 可能没有任何好处，因为最终将返回所有行。您可能想使用内部联接。
