Re: Outer Join workaround ... ideas

Jeffrey A. Rhines <jrhines(at)emain(dot)com wrote:

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

For brevity, rename tables
Category -> Cate(with fields cid, cname),
Product -> Prod(with fields pid, pname, pqty, cid).
Then try:

select pid, pqty, cid into tmptbl from Prod union all select 0 as pid, 0 as pqty, cid from Cate ;
select cid, sum(pqty) as cqty from tmptbl group by cid;

The first select produces a table in which there is at least one row in the Prod table for each Cate. The second
select does the summations over each cid. Since the dummy extra records all have zero quantity, the final sums are
correct. Doesn't use outer join. Doesn't use NULL.

Paul