Here is the SQL I am working with:
--
SELECT products.*, orders.response_code FROM "products" JOIN items ON
products.id = items.product_id
LEFT OUTER JOIN orders ON (items.order_id = orders.id AND
orders.response_code = '0' AND orders.user_id = 2) WHERE (permalink =
E'product-1' AND products.site_id = 1) LIMIT 1
--
An order has been paid when its response_code is 0.
One problem I have is that, if the user has not placed an order for
the product, than I still want the product fields to be returned so I
can't put this condition in a WHERE. If a user has paid an order for
that product, I want all fields from Product to be returned and I want
also the response_code to be returned (it will obviously be 0). But in
the current state of the query, even if there exists a paid order for
the current user_id, then the query misses it and therefore never
returns the response_code.
What's wrong with it?
