CODEWITHSUNDEEP
mysqlsql
User57
User57

Reputation: 2505

Where clause with multiple OR

I was trying to access list of foods with which has restaurant_id = 2 and if the food_id in carts table it should print cart_id and if the food_id in favorites table it should print favorite_id otherwise it will return null. But the problem i'm facing here is food_id i'm getting from that specific restaurant but while checking the carts and favorites id it fails to check for the user_id.

food_id is in the carts table but not for that user but it shows that cart_id. How do i make the query correct? 

Foods table: id, restaurant_id
Carts Table: id, user_id, food_id
Favorites Table: id, user_id, food_id

SQL QUERY:

SELECT foods.id, carts.id as cart_id , favorites.id as favorite_id 
FROM foods 
LEFT JOIN carts 
ON carts.food_id= foods.id 
LEFT JOIN favorites 
ON favorites.food_id= foods.id 
WHERE foods.restaurant_id = 2 
AND (carts.user_id = 1 OR favorites.user_id = 1)

Upvotes: 0

Views: 45

Answers (1)

Iłya Bursov
Iłya Bursov

Reputation: 24146

you need to use something like this:

SELECT foods.id, carts.id as cart_id, favorites.id as favorite_id
FROM foods
left join carts on carts.food_id=foods.id and carts.user_id = 1
LEFT JOIN favorites on favorites.food_id=foods.id and favorites.user_id = 1
WHERE foods.restaurant_id = 2

note that you need to filter both tables (cards/favorites) by user id

Upvotes: 3

Related Questions