Thursday, 12 September 2013

SQL left join between three tables with union

SQL left join between three tables with union

I have Products, from many Brands, which a User can favorite
(many-to-many) and also the User can follow Brands. And for the homepage I
need all the products which the user has added to favorites and the
products from Brands the User follows.
I have came with the following SQL query, which however, works not as
expected -- it returns only products which are from followed brands and
are in the same time in favorites.
SELECT * FROM products
INNER JOIN favorites ON products.id = favorites.favorable_id
INNER JOIN followings ON products.merchant_id = followings.followable_id
WHERE favorites.favorable_type = 'Product' AND favorites.user_id = ?
AND followings.followable_type = 'Merchant' AND followings.user_id = ?
How can I properly fix the query? Thanks in advance.

No comments:

Post a Comment