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:
junk=# select p.prod
junk-# , s1.units as store_1
junk-# , s2.units as store_2
junk-# , sAll.units as store_All
junk-# from product p
junk-# , sales s1
junk-# , sales s2
junk-# , sales sAll
junk-# where p.prod = s1.prod and s1.store=1
junk-# and p.prod = s2.prod and s2.store=2
junk-# and p.prod = sAll.prod and sAll.store in (1,2)
junk-# and p.prod='A' ;
prod | store_1 | store_2 | store_all
------+---------+---------+-----------
A | 50 | 100 | 50
A | 50 | 100 | 100
(2 rows)
--
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