Ambiguous column name

Question

I have the following SQL and it throws the error 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.

Answer 1

Your WHERE clause id needs to be more specific, include the table name:

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