Re: Screwy behavior with SUM and multiple joins to same

On Wed, 2002-09-04 at 11:23, Oliver Elphick wrote:
> On Tue, 2002-08-27 at 18:20, David Link wrote:
> > Screwy behavior with SUM and multiple joins to same table:
>
> The screwiness is in your joins:

Here's an alternative approach, that is a lot more readable:

SELECT prod, units AS store_1, 0 AS store_2, units AS store_3
WHERE store = 1
UNION
SELECT prod, 0 AS store_1, units AS store_2, units AS store_3
WHERE store = 2

but it will become unmanageable if there are a lot of stores.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"If any of you lack wisdom, let him ask of God, who
gives to all men generously and without reproach, and
it will be given to him." James 1:5