CODEWITHSUNDEEP
mysqlsqldatabase
Alexander George
Alexander George

Reputation: 871

Incomprehensible query behaviour

I have multiple tables, related by multiple foreign keys as in the following example:

You can see this relations represented in this image. enter image description here

So basically Contains is a bridge between Recipes and Ingredients.

The query I try to write it's supposed to give as result the names of the recipes whose ingredients type are "bovine" but not "lactic".

My attempt:

SELECT DISTINCT Recipes.name FROM Ingredients JOIN Contains USING(id_ingredient) JOIN Recipes USING (id_recipe) WHERE Ingredients.type = "bovin" AND Ingredients.type <> "lactic";

The problem is it still shows me recipes that have at least one lactic ingredient.

I would appreciate any help!

Upvotes: 0

Views: 74

Answers (5)

axxis
axxis

Reputation: 1004

You can use NOT EXISTS for this.

Try this:

SELECT DISTINCT Recipes.`name`
FROM Recipes JOIN Contains AS C1 USING (id_recipe) JOIN Ingredients USING(id_ingredient)
WHERE Ingredients.type = "bovin"
  AND NOT EXISTS (
    SELECT 1
    FROM Contains AS C2 JOIN Ingredients USING(id_ingredient)
    WHERE C1.id_recipe = C2.id_recipe
      AND Ingredients.type = "lactic"
)

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

A common solution of this type of question (checking conditions over a set of rows) utilizes aggregate + CASE.

SELECT R.Name
FROM Recipes R
INNER JOIN Contains C
 on R.ID_Recipe = C.ID_Recipe
INNER JOIN Ingredients I
 on C.ID_Ingredient = I.ID_Ingredient
GROUP BY R.name
having -- at least one 'lactic' ingredient
  sum(case when type = 'lactic' then 1 else 0 end) = 0
and    -- no 'bovin' ingredient
  sum(case when type = 'bovin' then 1 else 0 end) > 0

It's easy to extend to any number of ingredients and any kind of question.

Hijacked the fiddle of xQbert

Upvotes: 1

xQbert
xQbert

Reputation: 35323

SQL Fiddle In my example burgers and pasta have 'Bovin' and thus show up. So do cookies but cookies also have 'lactic' which is why they get excluded.

SELECT R.Name
FROM Recipes R
INNER JOIN Contains C
 on R.ID_Recipe = C.ID_Recipe
INNER JOIN Ingredients I
 on C.ID_Ingredient = I.ID_Ingredient
LEFT JOIN (SELECT R2.ID_Recipe
           FROM Ingredients I2
           INNER JOIN Contains C2 
             on C2.ID_Ingredient = I2.ID_Ingredient
           INNER JOIN Recipes R2               
             on R2.ID_Recipe = C2.ID_Recipe
           WHERE Type = 'lactic'
           GROUP BY R2.ID_Recipe) T3
 on T3.ID_Recipe = R.ID_Recipe
WHERE T3.ID_Recipe is null
 and I.Type = 'Bovin'
GROUP BY R.name

There likely is a more elegant way of doing this. I really wanted to CTE this and join it to itself.. but no CTE in mySQL. Likely a way to do this using exists too.... I'm not a big fan of using IN clauses as the performance generally suffers. Exists fastest, Joins 2nd fastest, in slowest (generally speaking)

The inline view (sub query) returns the ID_recipe of those you don't want to include.

The outer query returns the Name of the recipes with ingredients you want.

By joining these two together using an outer join we return all recipes and only those with the undesired ingredient. We then limit the results to only those where the recipe ID doesn't exist for the undesired ingredient. (undesired ingredient not found) you'll get only those recipes having all desired ingredients.

Upvotes: 0

Uueerdo
Uueerdo

Reputation: 15941

This is the general form of the kind of query you need:

SELECT *
FROM tableA
WHERE tableA.ID NOT IN (
   SELECT table_ID
   FROM ...
)
;

-- EXAMPLE BELOW --

The subquery gives the id values of all recipes that the "lactic" ingredient is used in, the outer query says "give me all the recipes not in that list".

SELECT DISTINCT Recipes.name
FROM Recipes
WHERE id_recipe IN (
   SELECT DISTINCT id_recipe 
   FROM `Ingredients` AS `i`
      INNER JOIN `Contains` AS `c` USING (id_ingredient)
   WHERE `i`.`type` = "lactic"
)
;

Alternatively, using your original query: You could've changed the second join to a LEFT JOIN, changed it's USING to an ON & included AND type = "lactic" there instead, and ended the query with HAVING Ingredients.type IS NULL (or WHERE, I just prefer HAVING for "final result" filtering). This would tell you which items could not be joined to the "lactic" ingredient.

Upvotes: 1

Marcelo
Marcelo

Reputation: 429

SELECT R.NAME
FROM CONTAINS C
INNER JOIN INGREDIENTS I
ON I.ID_INGREDIENTS = C.ID_INGREDIENTS AND I.TYPE = 'bovine' AND I.TYPE <> "lactic"
INNER JOIN RECIPES R 
ON R.ID_RECIPE = C.ID_RECIPE
GROUP BY R.NAME

That should work, maybe you need to escape 'contains'. It could be recognized as a SQL function.

Upvotes: 0

Related Questions