Re: a LEFT JOIN problem

On Sat, Oct 25, 2008 at 2:11 PM, Thomas <iamkenzo(at)gmail(dot)com> wrote:
> 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.

Put the filtering in your ON clauses. The WHERE clause is processed
after the JOINs are done, and is eliminating your results. Without
knowing your schema, this is just a guess..

SELECT products.*, paid FROM "products"
LEFT OUTER JOIN items ON (products.id = items.product_id AND title =
'some-product')
LEFT OUTER JOIN orders ON (items.order_id = orders.id AND paid = 1)
LEFT OUTER JOIN users ON (orders.user_id = users.id AND name = 'thomas')

> 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.

I suspect your app will need to to run the query and compare that
against the user(s) currently logged in.

Hope this helps!
Tony Wasson