Paul (et al),
That would certainly work (it's even one i considered, after the
email). Do you know what the overhead of creating and dropping a table
each query would be? A theoretical answer from the dev team would be
good. I'll eventually end up running some tests, and let the list know
the results. It seems like an issue many would run into.
Regards,
Jeff
Paul Condon wrote:
>
> Jeffrey A. Rhines <jrhines@emain.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