I have three tables using an Oracle database:
Recipe RID Cocktail Made_by 1 Daiquiri Otto 2 Brooklyn Lamp Bene 3 Mai Tai Otto 4 Brooklyn Lamp Bene 5 Brooklyn Lamp Otto 6 Drivers Glow Alfred Ingredients Ing_ID Ing-Name From_Where 1 Obstgeist Deutschland 2 Kuba-Rum Cuba 3 Limettensaft Spain 4 Obstgeist Deutschland 5 Grapefruit Deutschland 6 Zitronensaft Spain 7 Jamaika-Rum Jamaika 8 Martinique Italy 9 Curacao Venezuela 10 Mandelsirup Italy 11 Grapefruit Spain 12 Brombeersirup Deutschland Mix RID Ing_ID Quantity 1 2 60 1 3 30 2 4 40 2 5 10 2 6 20 3 7 30 3 8 30 3 9 15 3 10 8 3 3 20 4 4 40 4 5 10 4 3 15 5 4 40 5 5 10 5 3 10 5 6 10 6 11 150 6 12 30
The mix table interconnects Recipe and Ingredients. I am supposed to find the one cocktail made by the barman Otto which utilizes ingredients from Spain and Cuba. This is what I have tried until now:
SELECT DISTINCT r.rid, r.cocktail FROM recipe r join mix m on r.rid = m.rid JOIN ingredients i on m.ing_id = i.ing_id WHERE r.made_by LIKE 'Otto' AND i.ing_id IN (SELECT ing_id FROM ingredients WHERE from_where LIKE 'Spain') AND EXISTS (SELECT * FROM ingredients WHERE from_where LIKE 'Cuba');
Expected Output from this query:
RID Cocktail 1 Daiquiri
At the moment, I am just getting all the cocktails made by the barman Otto without distinction of the ingredients, like:
RID Cocktail 1 Daquiri 3 Mai Tai 5 Brooklyn Lamp
Since I am new to SQL, I do not know how to build up a synchronized query which searches for two strings at the same time or for two records in the same field. That was by far my best attempt. Every hint would be really appreciated! Thank you very much in advance
Advertisement
Answer
With this query you will get all the cocktails that have ingredients from Cuba and Spain and ingredients for that cocktails can also be from other countries as long as one is from Cuba and one is from Spain.
SELECT r.rid , r.cocktail FROM recipe r JOIN mix m on r.rid = m.rid JOIN ingredients i on m.ing_id = i.ing_id WHERE r.made_by LIKE 'Otto' AND i.From_Where IN ('Cuba', 'Spain') HAVING COUNT(DISTINCT i.From_Where) >= 2 GROUP BY r.rid , r.cocktail