Complicated query - help NEEDED!

Question

Hi, really hope someone can help me on this one! I have 6 tables:

Products
prodid

Prodequipment
prodid
equipclassmain
equipclassor

Equipclasses
classid

Equipfunctions
equipid
classid

Equipment
equipid

Workshopequipment
workshopid
equipid

Products – a list of some products
Equipment – a list of some equipment
Prodequipment – lists what equipment is needed to do a product. You can use equipment listed in equipclassmain or replace it by equipment in equipclassor. Table Products has one to many relation to table Prodequipment, i.e. you will use many different tools (equipment) to produce one product, but you can choose to use anyone in the pair equipclassmain/equipclassor. For instance to frame a photo you can use a wooden frame or plastic frame (one pair) and a cover glass or cover plastic (second pair). You can combine it as you wish, but both pairs should be used: wooden frame with cover glass or plastic frame with cover glass or wooden frame with plastic cover or plastic frame with plastic cover.
Equipfunctions and Equipclasses – Because one piece of equipment can be used in different ways it is not linked directly to table Prodequipment. I have created table Equipclasses where all single use of every possible equipment is listed and table Equipfunctions where I list those single uses for every equipment.
Workshopequipment – lists workshops and equipment they are using.

Now I need a list of products which can be manufactured by two different given workshops (let's say workshopid = 1 and workshopid = 4), i.e. both those workshops have all equipment needed to produce those products. Bear in mind that those workhops don't have to use the same equipment to do so as I described above.

I'm trying with this query:

SELECT prodid FROM Products JOIN (
    SELECT workshopid, prodlist, equipclassmain, equipclassor, 
     if( LOCATE( equipclassmain, prodlist ) >0 
      AND LOCATE( equipclassor, prodlist ) >0, 1, 0 ) AS pairstatus FROM Prodequipment JOIN
    (
     SELECT classid FROM Equipclasses JOIN (
      SELECT classid FROM Equipfunctions JOIN (
       SELECT workshopid, GROUP_CONCAT( equipid ) AS prodlist FROM Workshopequipment 
        GROUP BY workshopid
      ) 
      equipfunclist GROUP BY equipid 
     ) equipclasslist GROUP BY classid
    ) WorkshopequipmentList HAVING pairstatus = 1 AND workshopid in (1, 4)
) prodbyworkshops ON classid = equipclassmain OR classid = equipclassor

But I get an "Column classid in field list is ambiguous". Any idea what's wrong here?

THANK YOU!

Answer 1

Your query references multiple tables that have a classid column.
Therefore, when you reference classid in the HAVING clause, it doesn't know which table to get the classid for.

You need to write tablename.classid where tablename is the name of the table containing the classid column. (Probably equipclasslist; I didn't look at the query)

Answer 2

It's from the part of the query with:

...
SELECT classid 
FROM Equipclasses JOIN (
    SELECT classid
    FROM Equipfunctions...

Both EquipClasses and the inner query have a classId. You need to specify which you're selecting.