JOIN help needed!

Hi, really hope someone can help me on this one! The example which is close enough to my case would be as follows. Let say we have four tables:

Flowers – just a list of different flowers
_______________________
flowerid | flowername
1 | rose
2 | carnation
3 | lily
4 | cymbidium

Bouquet – just a pair of flowers
_________________________
flowerid1 | flowerid2
1 | 3
3 | 2
3 | 4
1 | 4

Shops – flower shops specializing in different flowers
______________________
shopid | flowerid
1 | 1
1 | 4
2 | 1
3 | 2
3 | 3

Customers – people who likes one particular flower
______________________
Customer | flowerid
Mary | 4
John | 1
Dan | 3
Jean | 2

Now I need a list of customers who might like ANY ONE of a pair of flowers in the bouquets above, but only made of the flowers available in BOTH shop 1 and 3.

I know I have to make a JOIN of some type, but none of my constructions seems to work with more than one shopid and only on one flowerid in the Bouquet table.

HELP PLEASE!

 

Try this query:

SELECT Customers.Customer FROM Customers,Bouquet,Shops WHERE (Customers.flowerid=Bouquet.flowerid1 OR Customers.flowerid=Bouquet.flowerid2) AND Customers.flowerid=Shops.flowerid AND (Shops.shopid=1 OR Shops.shopid=3) GROUP BY Customers.Customer;

Code (markup):

 

Hi Ruben, thanks a lot for your reply!
It was very helpfull, however an OR in the last condition means that I will have to go to buy flowers either to shop 1 or 3. I need a list of customers who I can satisfy by shopping in any of those two shops. This is my main problem in that example! …changing it to AND will return empty list.

 

My written query is return what you want!!! Or not??

 

Following might help..

I am not sure since the sample data itself is so limited to test various conditions.

Please test following query on little larger data and see if it gives desired output..

SELECT Customer, flowerid, shopid, flowerlist, flowerid1, flowerid2 FROM Customers JOIN
(
SELECT shopid, flowerlist, flowerid1, flowerid2, if(LOCATE(flowerid1, flowerlist) > 0 and LOCATE(flowerid2, flowerlist) > 0, 1, 0) AS pairstatus FROM Bouquet JOIN
(SELECT shopid, GROUP_CONCAT(flowerid) AS flowerlist FROM Shops GROUP BY shopid) ShopFlowerList HAVING pairstatus = 1 AND shopid in (1, 3)
) FlowerInShops ON flowerid = flowerid1 OR flowerid = flowerid2

 

Hi again guys,
Due to a non disclosure agreement I couldn’t post a real case and I tried to make it simple with this flower thing (no, my parents didn’t use flowers to talk about sex so no idea why I did it)…

Mastermunj, your query sounds god, but too complicated for me to translate from the flowers to my real case.

Let me try to use another example which is 100% my case, but with exchanged table names:

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 – as 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 workshops, i.e. both those workshops have equipment to do those products.

Sorry for waisting your time with the flowers! Now it’s as close to the real case it can be!

 

I guess if my previous query works well, should you not give it a try to make query for this one on your own?

At least giving it a try will help you learn better mysql and will save our time as well

 

I really tried to fit it, but I guess LOCATE is not a best option when we have prodid all integers and those additional tables Equipclasses and Equipfunctions screw it totally up for me