如何左连接两个具有相同列名的表

发布于 2024-12-15 12:05:42 字数 404 浏览 0 评论 0原文

我想左连接两个具有相同列名的表。我有两个表正在尝试加入，但我不断收到错误：

column 'id' is ambiguous

在 JSON 输出中返回。

我当前的查询：

$sQuery = "
 SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
 FROM   $sTable
 LEFT JOIN
    $sTable2
    ON ($sTable2.id = $sTable.id)

 $sWhere
 $sOrder
 $sLimit
";

产生该错误。当每个表中存在相同的列名时，如何将这两个表作为连接点连接起来？

原文

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:

column 'id' is ambiguous

returned in my JSON output.

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

惯饮孤独 2024-12-22 12:05:42

明确该列属于哪个表。这也适用于查询的 SELECT 部分：

SELECT table1.column AS column1, table2.column AS column2
FROM table1
LEFT JOIN table2
ON table1.column = table2.column

为了节省一些输入时间，请使用表别名：

SELECT t1.column AS column1, t2.column AS column2
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON t1.column = t2.column

Be explicit about which table the column belongs to. This also applies to the SELECT part of the query:

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

回复收藏 0 原文

白云悠悠 2024-12-22 12:05:42

歧义可能在于选择，而不是连接。连接看起来不错。 $aColumns 可能包含“id”，没有表名规范或其他内容。

回复收藏 0 原文

垂暮老矣 2024-12-22 12:05:42

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).

在您的情况下，表 1 和表 2 是相同的。因此，执行 Left Join 可能没有任何好处，因为最终将返回所有行。您可能想使用内连接。

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.

回复收藏 0 原文

~没有更多了~