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
