CODEWITHSUNDEEP
mysqlsqldatabase
Chris
Chris

Reputation: 456

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!

Upvotes: 1

Views: 55

Answers (3)

symcbean
symcbean

Reputation: 48357

The answer given by scaisEdge solves the problem you asked.

It's possible to extend this to support an indefinite number of ingredients, but its a bit messy adding a join to the table each time, hence...

 SELECT recipe_id, COUNT(DISTINCT name)
 FROM recipe
 WHERE name in (
     'Apple',
     'Orange',
      ...
 )
 GROUP BY recipe_id
 ORDER BY 1 DESC;

You might also add a HAVING COUNT(DISTINCT name)=xxx where xxx is your number of ingredients before the ORDER BY.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

If you have only two value for search you can use a inner join 

select a.recipe_id 
from my_table as a
inner join my_table as b on  a.recipe_id = b.recipe_id
where a.name ='Apple' 
and b.name ='Orange';

Upvotes: 2

Uueerdo
Uueerdo

Reputation: 15941

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 */

Upvotes: 2

Related Questions