Question 113250 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search