Re: a LEFT JOIN problem

On Thu, Oct 30, 2008 at 11:53:48PM +0100, Thomas wrote:
> 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

I find this formatting slightly unreadable, I find it much easier to
read code when it's formatted nicely:

SELECT p.*, o.response_code
FROM products p
INNER JOIN items i ON p.id = i.product_id
LEFT JOIN orders o ON i.order_id = o.id AND
o.user_id = 2 AND
o.response_code = '0'
WHERE p.permalink = 'product-1'
AND p.site_id = 1
LIMIT 1;

I'm guessing that "permalink" comes from the "products" relation, you
don't specify anywhere. Also, is "response_code" of some string type,
or is it a number?

If you've not used subqueries, an option could be:

SELECT *, (
SELECT MIN(response_code)
FROM items i, orders o
WHERE p.id = i.product_id
AND i.order_id = o.id
AND o.user_id = 2) AS response_code
FROM products p
WHERE permalink = 'product-1'
AND site_id = 1;

This will give you the minimum response code, as in later posts you
seemed to want to know this.

There are lots of options in SQL to write things differently, optimizing
for different things as well as just plain doing something else. I'd
also recommend going through a few SQL tutorials to get ideas of how to
solve different problems.

Sam