CODEWITHSUNDEEP
mysqlsqlsqliteselect
christian24
christian24

Reputation: 45

Ambigous column name error

I have an issue with select command from 2 tables.

So I have table1 with:

table1_id = int pk;    
table1_name;   
table1_surname;   
table1_age;    
table1_address;   
table1_city;

And table2 with:

table2_id int pk   
table1_id int fk references table1.table1_id;   
table3_id;  
table2_description;

When I write the following select statement, I get ambigous column name table1.table1_name error: 

SELECT table2.table2_id, table2.table1_id, table1.table1_name, table2.table2_description
from table1,
     table2 inner join
     table1
     on table2.table1_id = table1.table1_id;

Honestly I do not understand what is wrong about it?

Upvotes: 2

Views: 92

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Remove table1, just after from ( mixed old type "comma" and modern join syntaxes)

Use like the following :

SELECT t2.table2_id, t2.table1_id, t1.table1_name, t2.table2_description 
  FROM table2 t2 INNER JOIN table1 t1 ON ( t2.table1_id = t1.table1_id ) ;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Never use commas in the FROM clause. Always use proper, explicit JOIN syntax:

select t2.table2_id, t2.table1_id, t1.table1_name, t2.table2_description
from table1 t1 join
     table2 t2 
     on t2.table1_id = t1.table1_id;

The problem with your query is that you have two references to table1 because of the comma. You have mentioned the table twice. Hence, when you reference the column, the engine doesn't know what you are referring to. Your version is equivalent to:

from table1 cross join
     table2 join
     table1
     on table2.table1_id = table1.table1_id

table1 appears twice, so any reference to it is ambiguous.

You will notice that I also added table aliases to the query. Table aliases make the query easier to write and to read.

Upvotes: 0

PSK
PSK

Reputation: 17943

If i understood correctly, you have problem in below line

from table1, table2

In the above code you are using a CROSS JOIN between table2 and table1 which is not required in your case.

Change your query like following.

SELECT table2.table2_id, table2.table1_id, table1.table1_name, table2.table2_description 
from  table2 
 inner join table1 on table2.table1_id = table1.table1_id;

Upvotes: 3

Related Questions