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.Elphick@lfix.co.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