Kyokasuigetsu Kyokasuigetsu - 1 year ago 178
MySQL Question

1052: Column 'id' in field list is ambiguous

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

I could just select all the fields and avoid the error. But that would be a waste in performance. What should I do.

Answer Source

SQL supports qualifying a column by prefixing the reference with either the full table name:

SELECT,, name, section
  FROM tbl_names
  JOIN tbl_section ON = 

...or a table alias:

SELECT,,, s.section
  FROM tbl_names n
  JOIN tbl_section s ON = 

The table alias is the recommended approach -- why type more than you have to?

Why Do These Queries Look Different?

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download