CODEWITHSUNDEEP
sqlsql-serverjoin
Guarav T.
Guarav T.

Reputation: 458

SQL Server Inner join exclude results not working

I have 2 tables in my database t_recipe and t_recipe_ingredient having 1 to many relationship means one recipe can have multiple ingredients. I have to put a filter condition which should give me recipes which have ingredients included or excluded.

For included I have created below query and it works fine:

select * 
from t_recipe r 
join t_recipe_ingredient rexc  ON r.RecipeID = rexc.RecipeID 
where r.RecipeTypeID = 1 
  and rexc.IngrId in (110, 111)

But for excluded I am getting the recipes which have ingredients 110,111 however it should not return them, I think this is due to inner join which is including all other ingredients too and returning recipe:

select * 
from t_recipe r 
join t_recipe_ingredient rexc WITH (NOLOCK) ON r.RecipeID = rexc.RecipeID 
where r.RecipeTypeID = 1 
  and rexc.IngrId not in (110, 111)

Upvotes: 1

Views: 82

Answers (3)

Stefan
Stefan

Reputation: 111

I think you have to exclude the recipe totaly by using not exists 

select * from t_recipe r 
    where r.RecipeTypeID = 1
    and not exists(
     select null 
     from t_recipe_ingredient 
     where ingrid in(110, 111) and r.RecipeID = rexc.RecipeID 
   )

Upvotes: 3

I. Kavaliou
I. Kavaliou

Reputation: 1

Try the following:

select * 
from t_recipe r 
join t_recipe_ingredient rexc WITH (NOLOCK) ON r.RecipeID = rexc.RecipeID AND rexc.IngrId not in (110, 111)    
where r.RecipeTypeID = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269713

If you want recipes without those ingredients, here is one method:

select r.*
from t_recipe r left join
     t_recipe_ingredient rexc 
     on r.RecipeID = rexc.RecipeID and rexc.IngrId in (110, 111)
where r.RecipeTypeID = 1  and rexc.RecipeID is null;

Upvotes: 2

Related Questions