mysql query where not with Hierarchical tables

Question

So basically I'm joining 3 tables together. The main table is recipe, then it goes to ingredients list then ingredient.

So I need to have a query which has only recipes which contain NO chicken. The problem I am having is that because recipes have many ingredients when I use where != that just removes the ingredients with that meat but leaves the others.....how can i account for the multiple ingredients.

select Recipe.name as "No chicken"  from Recipe inner join IngredientList on Recipe.recipeId=IngredientList.recipeId inner join Ingredients on IngredientList.IngredientId=Ingredients.ingredientId where type!="chcicken" group by Recipe.name;

Also it seems if I just want chicken...so type="chicken" and that yields only chicken recipes ? — mespo365
– mespo365, Commented Apr 24, 2011 at 5:53

Bacon Bits · Accepted Answer · 2011-04-24 06:20:07Z

1

Your original statement has a GROUP BY with no aggregate function. That doesn't make sense. It should be an ORDER BY if you're trying to sort.

Try something like this:

SELECT `Recipe`.`name` AS "No chicken"
FROM `Recipe`
WHERE `Recipe`.`RecipeId` NOT IN (
    SELECT DISTINCT `IngredientList`.`RecipeId` AS `RecipeID`
    FROM `IngredientList`
        INNER JOIN `Ingredients` ON `IngredientList`.`IngredientId` = `Ingredients`.`IngredientId`
    WHERE `Ingredients`.`Type` = 'chicken'
)
ORDER BY `Recipe`.`name`

Depending on your schema, you may need to use SELECT DISTINCT in the main select statement if you're getting duplicate recipe names.

edited Apr 24, 2011 at 6:20

answered Apr 24, 2011 at 6:05

Bacon Bits

32.5k6 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Christo · Accepted Answer · 2011-04-24 07:26:24Z

The above have some typos, but Amirshk has a logically correct answer.

However, I recommend one avoid the IN() and NOT IN() clauses in MySQL as they are very, very slow on a set of tables as big as a large recipe database would get. IN and NOT IN can be re-written as joins to cut the runtime to 1/100th the time in MySQL 5.0. Even with MySQL 5.5's great improvements, the equivalent JOIN query benchmarks 1/5th the time on large tables.

Here is the revised query:

SELECT
Recipe.name AS "No Chicken"
FROM Recipe LEFT JOIN
    (
    SELECT IngredientList.recipeId, Ingredients.ingredientId
    FROM IngredientList JOIN Ingredients USING (IngredientId)
    WHERE Ingredients.type = 'chicken'
    ) WithChicken
    ON Recipe.recipeId = WithChicken.recipeId
WHERE WithChicken.recipeId IS NULL;

This is pretty obtuse, so here is simplified SQL that provides the key concept of the NOT IN(...) equivalent exclusion join:

SELECT whatever FROM x
WHERE x.id NOT IN (
    SELECT id FROM y
};

becomes

SELECT whatever FROM x
LEFT JOIN y ON x.id = y.id
WHERE y.id IS NULL;

Amirshk · Accepted Answer · 2011-04-24 05:25:02Z

0

Use an inner query to filter recipes with chicken, then select all the recipes without them.

As so:

select
    Recipe.name as "No chicken"
    from Recipe
        inner join IngredientList on Recipe.recipeId=IngredientList.recipeId
        inner join Ingredients on IngredientList.IngredientId=Ingredients.ingredientId
    where Recipe.recipeId NOT IN (
        select
        Recipe.recipeId
        from Recipe
            inner join IngredientList on Recipe.recipeId=IngredientList.recipeId
            inner join Ingredients on IngredientList.IngredientId=Ingredients.ingredientId
            type ="chcicken" group by Recipe.recipeId)

answered Apr 24, 2011 at 5:25

Amirshk

8,2682 gold badges38 silver badges64 bronze badges

Collectives™ on Stack Overflow

mysql query where not with Hierarchical tables

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related