CODEWITHSUNDEEP
sql-servert-sql
mbcharney
mbcharney

Reputation: 355

Need to exclude all rows based on one where condition

I have a database with three tables. One table is called recipe with these columns:

EntryDate, Recipename, IngredientName, IngredientQty, QtyMeasurement

There are only two recipes in the database at this time. The first recipe, called Kitchen Sink Cookies, has 17 ingredients and the second recipe, called No Bake Cookies II, has only 6 ingredients.

Recipe 1 (Kitchen Sink) uses eggs as an ingredient. I need to find all recipes that do not use eggs.

So far I can only get it to return either both recipes or nothing. I have googled for a few hours now but am unable to get a query to work.

This is the latest query I have tried and it returns nothing.

Use recipes;
Go

Select
    RecipeName
From
    recipe
Where
    RecipeName Not In
    (
        select
            RecipeName
        from
            recipe
        where
            IngredientName Not Like 'EGG%'
        Group By
            RecipeName
    );
Go

So what I need to happen is if a recipe has an ingredient that is egg I need to exclude the recipe name from the output.

Does anyone have any ideas, or links that I can view (That I haven't looked at yet), or code samples?

Thanks for any help in advance.

Upvotes: 1

Views: 114

Answers (2)

jigga
jigga

Reputation: 614

You need to get rid of the NOT LIKE...

    Use recipes;
    Go

    Select
        RecipeName
    From
        recipe
    Where
        RecipeName Not In
        (
            select
                RecipeName
            from
                recipe
            where
                IngredientName IN ('EGG','EGGS')
            Group By
                RecipeName
        );
    Go

Also, as mentioned in the comments I would recommend not to use the LIKE operator because you will get no results for recipes with egg plants f.e.

A better approach would be:

    WHERE IngredientName IN ('EGG','EGGS')

Upvotes: 0

Sahi
Sahi

Reputation: 1484

is this helpful?

    SELECT * FROM recipe WHERE Recipename NOT IN
    (SELECT Recipename FROM recipe WHERE IngredientName LIKE 'EGG%')

OR

    SELECT r1.* from recipe r1
    LEFT JOIN recipe r2 on r1.Recipename = r2.Recipename AND r2.IngredientName LIKE 'EGG%'
    WHERE r2.Recipename is NULL

Upvotes: 1

Related Questions