1052: Column 'id' in field list is ambiguous

Question

I have 2 tables. tbl_names and tbl_section , in which there is an id field. How do I select the id field because I always get this error:

 1052: Column 'id' in field list is ambiguous 

Here is my request:

 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 it will be a waste of time. What should I do?

Answer 1

SQL supports column classification by prefixing a reference with the fully qualified 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 table alias:

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

Table Alias ​​- Recommended Approach - Why Enter More Than You Need?

Why do these queries look different?

Secondly, my answers use the ANSI-92 JOIN syntax (your ANSI-89). While they do the same, the ANSI-89 syntax does not support OUTER connections (RIGHT, LEFT, FULL). The ANSI-89 syntax should be considered obsolete, many of the SOs that will not vote for the ANSI-89 syntax to reinforce this. For more information see this question .

Answer 2

In your SELECT you need to specify your identifier with the table from which you want to select it.

 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 

Answer 3

You would do this by providing the full name, for example:

 SELECT tbl_names.id as id, name, section FROM tbl_names, tbl_section WHERE tbl_names.id = tbl_section.id 

What will give you the tbl_names identifier

Answer 4

There are already many answers to your question, you can do it the same way. You can give your table an alias and use it in a select query, for example:

 SELECT a.id, b.id, name, section FROM tbl_names as a LEFT JOIN tbl_section as b ON a.id = b.id; 

Answer 5

What you probably really want to do is use the join operator as follows:

 (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 him https://dev.mysql.com/doc/refman/5.0/en/union.html

Answer 6

If the format of the identifier in the two tables changes, then you want to join them, so you can choose to use the identifier from one main table, say, if you have table_customes and table_orders , and the tha id for orders is similar to " 101 ", " 102 " ... " 110 ", just use it for customers

 select customers.id, name, amount, date from customers.orders; 

Answer 7

 SELECT tbl_names.id, tbl_names.name, tbl_names.section FROM tbl_names, tbl_section WHERE tbl_names.id = tbl_section.id