Hi,
I have the following tables:
Product(id, title, price)
Item(id, product_id, order_id, quantity)
Order(id, user_id, amount, paid)
User(id, name)
What I want to achieve is a query on a specific Product based in its
title. If the product has at least 1 order in which it appears, then
return the Product and Order details, if the product has no paid order
associated, then only return the Product fields.
I have tried the following query:
--
SELECT products.*, paid FROM "products"
LEFT OUTER JOIN items ON products.id = items.product_id
LEFT OUTER JOIN orders ON items.order_id = orders.id
LEFT OUTER JOIN users ON orders.user_id = users.id
WHERE (title = E'some-product' AND paid = 1 AND name = 'thomas')
--
The problem with my query, is that if there are no paid associated
orders, then the WHERE will drop every returned line that has paid =
0, therefore I don't get anything at all, but I would like to have at
least the products field.
Moreover, the "name" argument in the WHERE comes from the user logged
in data. So if the user is not logged in, no fields are returned.
Is that possible to achieve in only one query? What is the correct way
of doing it?
