I have 2 tables. tbl_names
and tbl_section
which has both the id
field in them. How do I go about selecting the id
field, because I always get this error:
1052: Column 'id' in field list is ambiguous
Here's my query:
SELECT id, name, section FROM tbl_names, tbl_section WHERE tbl_names.id = tbl_section.id
I could just select all the fields and avoid the error. But that would be a waste in performance. What should I do?
One of the simplest ways to solve an “ambiguous name column” error — without changing column name — is to give the tables you want to join an alias. This sends a clear information to the SQL Machine the columns are different.
To fix this, simply add the tablename or alias for the table you want to work with. If you are writing the query yourself, this is a bit easier to deal with as you will know which table you meant to use. In our example, we should add the alias for the oc_customer table, c, to the column names.
You may see an error that says something like Column 'id' in field list is ambiguous . This error means that there is a field name that is present in more than one table, so it needs to be scoped with the table name to avoid ambiguity: using orders.id instead of just id will resolve the issue.
SQL supports qualifying a column by prefixing the reference with either the full table name:
SELECT tbl_names.id, tbl_section.id, name, section FROM tbl_names JOIN tbl_section ON tbl_section.id = tbl_names.id
...or a table alias:
SELECT n.id, s.id, n.name, s.section FROM tbl_names n JOIN tbl_section s ON s.id = n.id
The table alias is the recommended approach -- why type more than you have to?
Secondly, my answers use ANSI-92 JOIN syntax (yours is ANSI-89). While they perform the same, ANSI-89 syntax does not support OUTER joins (RIGHT, LEFT, FULL). ANSI-89 syntax should be considered deprecated, there are many on SO who will not vote for ANSI-89 syntax to reinforce that. For more information, see this question.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With