Error in MySQL : field list is ambiguous but not able to get the reason

Question

I have 3 tables vendors, components and catalog, here is the schema:

vendors - vid,vaddress,vname components- cid, cname, type catalog- cid, vid, cost

catalog is connected with the 2 tables with a foreign key vid and cid.

Query:

select vid
from catalog c,
     components com,
     vendors v
where com.cid = c.cid and c.vid = v.vid and v.vaddress = 'Noida'
   or c.cname = 'antenna';

ERROR 1052 (23000): Column 'vid' in field list is ambiguous.

I am getting this error.

Change select vid to select c.vid . – Grumpy Oct 10 '22 at 07:13 — Grumpy, Oct 10 '22 at 07:13

score 1 · Accepted Answer · answered Oct 10 '22 at 07:13

1

You need to select c.vid. Even when they are the same by the condition, you need to specivy which value you want to select, since just vid could be eigther c.vid or v.vid

answered Oct 10 '22 at 07:13

csoeger

61
6

`You need to select c.vid` ... no, selecting `v.vid` would also be valid. – Tim Biegeleisen Oct 10 '22 at 12:32

score 1 · Answer 2 · answered Oct 10 '22 at 07:14

Both the components and vendors tables have a vid column, so selecting vid without a scoping alias or table name is ambiguous because it is not clear which table you are referring to. On top of this, you should be using modern explicit joins. Consider this version:

SELECT v.vid
FROM catalog c
INNER JOIN components com ON com.cid = c.cid
INNER JOIN vendors v ON c.vid = v.vid
WHERE v.vaddress = 'Noida' OR c.cname = 'antenna';

Error in MySQL : field list is ambiguous but not able to get the reason

2 Answers2