CODEWITHSUNDEEP
sqlselectmany-to-many
MikeOx
MikeOx

Reputation: 177

many-to-many select with complex condition

I have the tables:

+------------+
| Ingredient |
+------------+
| id         |
+------------+
| name       |
+------------+

+---------------+
| Relingredient |
+---------------+
| id_ingredient |
+---------------+
| id_recipe     |
+---------------+

+--------+
| Recipe |
+--------+
| id     |
+--------+
| name   |
+--------+

And I need to Select Recipes that have the ingredients that I want (ALL the ingredients pass to them) I tried this: 

SELECT R.id, R.nom FROM Recipe R, Relingredient RI, Ingredient I 
WHERE  R.id = RI.id_recipe AND RI.id_ingredient = I.id AND I.name='onion' AND I.name='oil' 
GROUP BY R.name

but retuns zero rows

I also tried this:

SELECT R.id, R.nom FROM Recipe R, Relingredient RI, Ingredient I 
WHERE  R.id = RI.id_recipe AND RI.id_ingredient = I.id AND (I.name='onion' or I.name='oil') 
GROUP BY R.name

But it selects all recipes that have onion or oil, not only the ones wich haves onion AND oil ... What can I do?

(edit) sample of what I want:
for example I have the recipes:
1: grilled chicken(ingredients: chicken, onion, oil)
2: chinese soup(ingredients: pork, onion, oil, noodles)
3: vegetable sandwich (ingredients: bread, oil, tomato, salad)
The query should return just the recipes: grilled chicken and chinese soup

Thanks for Helping me!!

Upvotes: 0

Views: 106

Answers (3)

Majid Akbari
Majid Akbari

Reputation: 210

Try this :

SELECT R.id, R.name FROM Recipe R
       Where R.Id in (
                         Select RI.Id_recipe From Relingredient as RI 
                                INNER JOIN Ingredient as I ON  RI.Id_ingredient = I.Id
                                WHERE I.name = 'onion'
                                      OR I.name='oil'
                     )

I got it completely. New query:

SELECT  *
        FROM    dbo.Recipt
        WHERE   Id IN ( SELECT  id_recipe
            FROM    dbo.Relingredient
                    INNER JOIN dbo.Ingredient ON id_ingredient = Id
            WHERE   Name = 'oil' )
    AND Id IN ( SELECT  id_recipe
                FROM    dbo.Relingredient
                        INNER JOIN dbo.Ingredient ON id_ingredient = Id
                WHERE   Name = 'union' )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

To get exactly the ingredients you are looking for, use a having clause:

SELECT R.id, R.nom
FROM Recipe R JOIN
     Relingredient RI
     ON R.id = RI.id_recipe JOIN
     Ingredient I 
     ON RI.id_ingredient = I.id
GROUP BY R.name
HAVING SUM(CASE WHEN I.name = 'onion' THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN I.name = 'oil' THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN I.name NOT IN ('onion', 'oil') THEN 1 ELSE 0 END) = 0;

Each condition in the having clause checks one of the conditions. The first checks that at least one ingredient is "onion", the second that at least one is "oil", and the third that no other ingredients are present. Remove the last clause if you want to allow other ingredients.

This method is quite flexible for different conditions on the ingredients.

EDIT:

The following having clause lets you name the ingredients only once. But you need to also insert the count:

HAVING COUNT(DISTINCT CASE WHEN I.name IN ('onion', 'oil') THEN I.name END) = COUNT(DISTINCT NAME) AND
       COUNT(DISTINCT NAME) = 2;

The distinct is in case one recipe can include an ingredient more than once.

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try something like this:

SELECT
  R.id, R.name
FROM Recipt R
  JOIN Relingredient RI
    ON R.Id = RI.Id_recipe
  JOIN Ingredient I
    ON RI.Id_ingredient = I.Id
WHERE I.name = 'onion'
    OR I.name='oil'
GROUP BY R.id, R.name
HAVING COUNT(I.name) = 2

Upvotes: 1

Related Questions