CODEWITHSUNDEEP
mysqlsqlmariadb
Giorgio
Giorgio

Reputation: 173

MySql query join tables

I've got the following tables in my Database:

dishes

id
name

users

id
email

ingredients

id
name

dishes_ingredients (this is used to represent the relation between dishes and ingredients)

id
dish_id
ingredient_id

users_allergic_ingredients (users are allergic to certain ingredients)

id
user_id
ingredient_id

Given a certain user (user_id), I need to get all the dishes to which the user is NOT allergic to.

I tried with this query:

SELECT *
FROM dishes
JOIN dishes_ingredients
ON dishes.id = dishes_ingredients.dish_id
WHERE dishes_ingredients.ingredient_id NOT IN
        (SELECT uai.ingredient_id FROM users_allergic_ingredients uai WHERE uai.user_id = 1)

But I get all the dishes and not only the dishes to which the customer is not allergic to.

What am I doing wrong?

Upvotes: 0

Views: 61

Answers (2)

Lihi
Lihi

Reputation: 19

It sounds wired, but you can use left join to users_allergic_ingredients and get only the rows the join to didn't succeed. like this - 

SELECT D.name
FROM dishes D
INNER JOIN dishes_ingredients DI
ON D.id = DI.dish_id
LEFT OUTER JOIN users_allergic_ingredients  UAI
ON UAI.user_id = @user_id 
and UAI.ingridient_id = DI.ingridient_id
WHERE UAI.ingridient_id IS NULL

notice the WHERE who deliberately asks the rows that were left out

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269733

Your are getting ingredients that are absent, not dishes.

I would approach this using aggregation:

SELECT di.dish_id
FROM dishes_ingredients di LEFT JOIN
     users_allergic_ingredients uai
     ON uai.ingredient_id = di.ingredient_id AND
        uai.user_id = 1
GROUP BY di.dish_id
HAVING COUNT(uai.integredient_id) = 0;

The FROM clause matches uai when the user is allergic to the ingredient. The HAVING clause just determines that the count is zero.

Upvotes: 1

Related Questions