SQL 不明确的列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3230023/
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
Ambiguous column name
提问by Cameron
I have the following SQL and it throws the error Ambiguous column name 'id'
我有以下 SQL，它抛出错误 Ambiguous column name 'id'
select tbl_registration.*, tbl_ebp.name as ebp_name, tbl_Users.id as user_id, tbl_ebp.id as linked_ebp_id
from tbl_registration
left outer join tbl_ebp on tbl_ebp.id = tbl_registration.ebp_id
left outer join tbl_users on tbl_registration.email = tbl_users.username
where id = [PARAM]p_id
I've read some articles on this, but can't find a working solution for my code. Any help much appreciated.
我已经阅读了一些关于此的文章，但找不到适用于我的代码的可行解决方案。非常感谢任何帮助。
回答by Adam Houldsworth
Your WHERE clause id needs to be more specific, include the table name:
您的 WHERE 子句 ID 需要更具体，包括表名：
WHERE table.id = [PARAM]p_id
If two things share the same name, this is where the ambiguity steps in. In this case multiple tables in your SQL contain the "id" column.
如果两个事物共享相同的名称，这就是歧义的地方。在这种情况下，SQL 中的多个表包含“id”列。
SQL has the intelligence to disambiguate column names if the column name is unique across the current set of tables being touched - hence most of the time you don't need to prefix column names with table names.
如果列名在当前所接触的表集中是唯一的，则 SQL 具有消除列名歧义的智能 - 因此大多数时候您不需要用表名作为列名的前缀。
回答by Steven A. Lowe
most likely more than one table has a column named id; use a table prefix in the where clause
很可能不止一个表有一个名为 id 的列；在 where 子句中使用表前缀
回答by Brian Hooper
Have you tried prefixing the id column name in the where clause?
您是否尝试过在 where 子句中为 id 列名称添加前缀？
回答by David
It's referring to "id" in your where clause. You need to specify which table's "id" it should filter.
它指的是 where 子句中的“id”。您需要指定它应该过滤哪个表的“id”。
回答by SagarPPanchal
I have replaced your code
我已经替换了你的代码
select *, tbl_ebp.name as ebp_name, tbl_Users.id as user_id, tbl_ebp.id as linked_ebp_id
from tbl_registration
left outer join tbl_ebp on tbl_ebp.id = tbl_registration.ebp_id
left outer join tbl_users on tbl_registration.email = tbl_users.username
where your_respective_tblname.id = your_respective_tblname.[PARAM]p_id
