multiple table join and sumation

Hi All,

I am trying to join a number of tables and sum different quantities for each
unique identifier. I would like one record back for each identifier.

The first table contains all of the possible items. The second and third
tables contain sales orders and items respectively. The forth and fifth
tables contains the purchase orders and items respectively.

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? I am currently stumbling
around with a few SELECT and UNION statements but I am getting multiple
records back for each item. TIA

Kind Regards,
Keith

PS I will eventually be adding another table(s) but I expect that once I learn
this process it will be extensible.

tbl_item
id | ...
------+...
A | ...
B | ...
C | ...
D | ...
E | ...

tbl_sales
order | closed |...
------+--------+...
1 | false |...
2 | true |...
3 | true |...
4 | false |...
5 | false |...

tbl_sales_item
order | id | quantity
------+-------+---------
1 | A | 10
1 | B | 5
2 | C | 3
3 | B | 2
3 | D | 20
4 | B | 4
5 | A | 5

tbl_purchase
order | closed |...
------+--------+...
21 | false |...
22 | true |...
23 | false |...

tbl_purchase_item
order | id | quantity
------+-------+---------
21 | A | 10
21 | B | 5
22 | C | 3
22 | D | 15
23 | E | 2

id | committed | on_order
------+-----------+---------
A | 15 | 10
B | 9 | 5
C | 0 | 0
D | 0 | 0
E | 0 | 2

______________________________________________
99main Internet Services http://www.99main.com