1052 Column id in field list is ambiguous

Question

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

User · Accepted Answer

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?

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.

User · Answer

What you are probably really wanting to do here is use the union operator like this    select ID from Logo where AccountID   1 and Rendered    True     union    select ID from Design where AccountID   1 and Rendered    True     order by ID limit 0  51   Here s the docs for it https   dev mysql com doc refman 5 0 en union html

User · Answer

The simplest solution is a join with USING instead of ON  That way  the database  knows  that both id columns are actually the same  and won t nitpick on that   SELECT id  name  section   FROM tbl names   JOIN tbl section USING  id    If id is the only common column name in tbl names and tbl section  you can even use a NATURAL JOIN   SELECT id  name  section   FROM tbl names   NATURAL JOIN tbl section   See also  https   dev mysql com doc refman 5 7 en join html

User · Answer

In your SELECT statement you need to preface your id with the table you want to choose it from    SELECT tbl names id  name  section  FROM tbl names INNER JOIN tbl section     ON tbl names id   tbl section id   OR  SELECT tbl section id  name  section  FROM tbl names INNER JOIN tbl section     ON tbl names id   tbl section id

User · Answer

You would do that by providing a fully qualified name  e g    SELECT tbl names id as id  name  section FROM tbl names  tbl section WHERE tbl names id   tbl section id   Which would give you the id of tbl names

User · Answer

Already there are lots of answers to your question  You can do it like this also  You can give your table an alias name and use that in the select query like this      SELECT a id  b id  name  section FROM tbl names as a  LEFT JOIN tbl section as b ON a id   b id

User · Answer

If the format of the id s in the two table varies then you want to join them  as such you can select to use an id from one-main table  say if you have table customes and table orders  and tha id for orders is like  101   102     110   just use one for customers  select customers id  name  amount  date from customers orders

User · Answer

SELECT tbl names id  tbl names name  tbl names section   FROM tbl names  tbl section   WHERE tbl names id   tbl section id

[mysql] 1052: Column 'id' in field list is ambiguous

Why Do These Queries Look Different?

Examples related to mysql

Examples related to sql

Examples related to database

Examples related to join

Examples related to mysql-error-1052