CODEWITHSUNDEEP
sqlmysqljoinmysql-error-1052
Wern Ancheta
Wern Ancheta

Reputation: 23307

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

Upvotes: 140

Views: 575282

Answers (7)

vog
vog

Reputation: 25617

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

Upvotes: 6

M J
M J

Reputation: 2991

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;

Upvotes: 8

Bryan Legend
Bryan Legend

Reputation: 6896

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

Upvotes: 5

Festole
Festole

Reputation: 11

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;

Upvotes: 1

Taryn
Taryn

Reputation: 247760

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

Upvotes: 23

OMG Ponies
OMG Ponies

Reputation: 332641

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.

Upvotes: 210

halfdan
halfdan

Reputation: 34224

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

Upvotes: 8

Related Questions