From: | "Jeffrey A(dot) Rhines" <jrhines(at)email(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | OUTER JOIN workaround... ideas? |
Date: | 2000-07-06 20:33:20 |
Message-ID: | 3964ED10.ACE06D18@email.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hey all,
I've got a situation where i would use an OUTER JOIN if i could, and
i've found a work-around, but it is significantly less than elegant.
The example:
Tables:
Category, with fields id and name;
Product, with fields id, name, quantity, and categoryId.
All products have a valid categoryId, but not all categories will have a
corresponding product. I want a query that gives me output containing:
category.name and coalesce(sum(product.quantity), 0). That is, i want a
summary of the number of products i have on stock for a particular
category, and if no products exist, i want a 0 (instead of a null).
Typically, i would do:
select category.name, coalesce(sum(product.quantity), 0)
from category, product
where product.categoryId =* category.id
(The =* is short hand for an OUTER JOIN, giving me all rows in category,
and only those rows from product that have a valid categoryId, without
limiting the rows returned from category)
The workaround i've found (so far) is:
select category.name,
(select coalesce(sum(product.quantity), 0)
from product
where product.categoryId = category.id) as quantity
from category;
It seems like there should be a more efficient way than a sub query.
Any ideas, or am i just being picky?
Best Regards,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Wieck | 2000-07-06 21:09:27 | Re: [SQL] Re: lztext and compression ratios... |
Previous Message | Jurgen Defurne | 2000-07-06 18:09:09 | Re: Re: [HACKERS] pl/pgsql function out parameters |