SQL left join table with just some rows by id

user_13652624 0
Viewed 37

I am very new to SQL and I know that this is pretty basic but I just don't find a solution. I have two tables which I want to LEFT-Join together, but the problem is that I need from one of the tables just some rows. Here is the query I have:

select i.recipeid
from ingredients i left join useringredients ui
on ui.ingredient = i.ingredient
group by i.recipeid
having count(*) = count(ui.ingredient);

Now I just want to join the whole ingredients table with the user ingredients tables part where the user_id = 1. I hope you can help me. Thank you!

An Example:

In the ingredients table I have:

enter image description here

In the user ingredients table I have:

enter image description here

Now I want for the user id 0 to return recipe 14 because he has "Mehl" and "Butter" but for user id 1 I want to return nothing because he just has "Butter" but no "Mehl".

sqlite mysql sql database
1 Answers

You need the condition ui.id = ? in the ON clause:

select i.recipeid
from ingredients i left join useringredients ui
on ui.ingredient = i.ingredient and ui.id = 0
group by i.recipeid
having count(*) = count(ui.ingredient);
user_104988280
Related