SQL: Search two strings in the same column through subquery-CodePudding

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

CodePudding user response：

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

DEMO