am 03.12.2004, um 16:33:40 -0500 mailte Keith Worthington folgendes:
> Hi All,
>
> I am trying to join three tables and sum the quantities.
>
> The first table contains all of the possible items. The second table contains
> orders and the third table contains the items on each order.
>
> For all items found in tbl_item I need the total quantity on open orders. If
> an item is not on any open order the quantity should be zero.
>
> Can someone please explain the best way to do this?
>
> tbl_item
> id | ...
> ------+...
> AB12 | ...
> CD34 | ...
> EF34 | ...
> GH12 | ...
> JK56 | ...
>
> tbl_order
> order | closed |...
> ------+--------+...
> 1 | false |...
> 2 | true |...
> 3 | true |...
> 4 | false |...
> 5 | false |...
>
> tbl_item
> order | id | quantity
> ------+-------+---------
> 1 | AB12 | 10
> 1 | CD34 | 5
> 2 | CD34 | 3
> 3 | EF34 | 2
> 3 | GH12 | 20
> 4 | GH12 | 4
> 5 | AB12 | 5
>
> id | quantity
> ------+---------
> AB12 | 15
> CD34 | 5
> EF34 | 0
> GH12 | 4
> JK56 | 0
>
test_db=# select * from tbl1; id
------AB12CD34EF34GH12JK56
(5 Zeilen)
test_db=# select * from tbl2;order_id | closed
----------+-------- 1 | f 2 | t 3 | t 4 | f 5 | f
(5 Zeilen)
test_db=# select * from tbl3;order_id | id | quantity
----------+------+---------- 1 | AB12 | 10 1 | CD34 | 5 2 | CD34 | 3 3 |
EF34| 2 3 | GH12 | 20 4 | GH12 | 4 5 | AB12 | 5
(7 Zeilen)
Now i have this sql:
select id, sum(summe) from (select id, sum(quantity) as summe from tbl3 where order_id in (select order_id from tbl2
whereclosed = false) group by id union select id,0 from tbl1 group by id) as x group by x.id order by x.id;
and this result:
id | sum
------+-----AB12 | 15CD34 | 5EF34 | 0GH12 | 4JK56 | 0
(5 Zeilen)
Andreas
--
Andreas Kretschmer (Kontakt: siehe Header) Tel. NL Heynitz: 035242/47212
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net=== Schollglas Unternehmensgruppe ===