Query help needed!

[astralex]

Hi, really hope someone can help me on this one!

We 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. We have created table Equipclasses where all single use of each equipment is listed and table Equipfunctions where we list those single uses for every equipment.

Workshopequipment – lists workshops who have different equipment.

Now I need a list of products which can be manufactured by two different given workshops (let's say workshopid = 1 and workshopid = 5), i.e. both those workshops have all equipment needed to produce those products.

THANK YOU!

 

[PHV]

A starting point:
SELECT equipid FROM Workshopequipment GROUP BY equipid HAVING COUNT(*)>1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[astralex]

Hi and thanx! However I'm not sure it's a right way to go. For the first I need a select on Products to have a list of products which can be done let's say by workshopid = 1 and 5. And those workshops can use different equipment to produce those products as listed in pairs in table Prodequipment. As in example above workshop 1 can use a wooden frame and a glass cover and workshop 5 can use a plastic frame and a plastic cover, but both will come up with a framed picture.