Chris Chris - 4 months ago 7
SQL Question

MYSQL find all rows where second row have given values

my database table looks like:

╔════╦═══════════╦═══════════╗
║ ID ║ RECIPE_ID ║ NAME ║
╠════╬═══════════╬═══════════╣
║ 1 ║ 1 ║ Apple ║
║ 2 ║ 2 ║ Apple ║
║ 3 ║ 2 ║ Orange ║
║ 4 ║ 3 ║ Kiwi ║
║ 5 ║ 1 ║ Kiwi ║
║ 6 ║ 3 ║ Cherry ║
║ 7 ║ 3 ║ Banana ║
╚════╩═══════════╩═══════════╝


When i'm querying mysql for
"Apple"
AND
"Orange"
, so i should get the
RECIPE_ID
2 because
"Apple"
and
"Orange"
have the same
RECIPE_ID

or second example:

When looking for
"Kiwi"
AND
"Banana"
i should get the
RECIPE_ID
3

Here is my SQL I have tried

SELECT recipe_id, name
FROM foodtipps.rezepte_zutaten
WHERE name='Kiwi' AS 'NAME1' AND
name='Banana AS 'NAME2' GROUP BY recipe_id


Hope you understand my problem.
Thank you!

Answer

This can be extended to many more ingredients easily:

SELECT recipe_id
FROM theTable
WHERE name IN ('Apple', 'Orange')
GROUP BY recipe_id
HAVING COUNT(*) = 2 /* number of ingredients in the list */