CODEWITHSUNDEEP
sqljoin
Anas Najem
Anas Najem

Reputation: 27

Column in field list is ambiguous error

i've been recently working in mysql and in one of the requests i wrote :

SELECT SIGLE_EEP, ID_SOUS_MODULE, LIBELLE 
FROM mef_edi.eep a, mef_edi.envoi e, mef_edi.sous_module s 
WHERE a.ID_EEP =  e.ID_EEP 
AND a.ID_SOUS_MODULE = s.ID_SOUS_MODULE;

and they told me :

Column ID_SOUS_MODULE in field list is ambiguous

What should i do ?

Upvotes: 1

Views: 1193

Answers (2)

Random Developer
Random Developer

Reputation: 1344

I agree with the answer above, you may have duplicate column names across your 3 tables, assigning the table id (a, e, s) as noted above will avoid that issue in the select. In addition to what @juergen said you may want to get rid of that cartesian join by using an inner or left join (inner seems to be what your going for). The way you are joining your table you are joining every possible combination of rows together than filtering. using a proper join will get you better performance in the long run as your table line counts grow. Here is an example of a non cartesian join:

SELECT SIGLE_EEP, ID_SOUS_MODULE, LIBELLE 
FROM mef_edi.eep a
INNER JOIN mef_edi.envoi e ON (a.ID_EEP =  e.ID_EEP) 
INNER JOIN mef_edi.sous_module s ON (a.ID_SOUS_MODULE = s.ID_SOUS_MODULE)

Upvotes: 0

juergen d
juergen d

Reputation: 204894

More than one table has a column named ID_SOUS_MODULE.

So you need to name the table every time you mention the column to specify which table you mean.

Change

SELECT ID_SOUS_MODULE

for instance to 

SELECT a.ID_SOUS_MODULE

Upvotes: 2

Related Questions