Thread: 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

On Fri, Dec 03, 2004 at 06:16:32PM -0500, Keith Worthington wrote:

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

The following gives the above results when used against your example
data.  I look at it and think it ought to be simpler, so maybe somebody
will post an improvement.

SELECT i.id,
       COALESCE(s.sum, 0) AS committed,
       COALESCE(p.sum, 0) AS on_order
FROM tbl_item AS i
LEFT OUTER JOIN (
  SELECT id, SUM(quantity)
  FROM tbl_sales_item JOIN tbl_sales USING ("order")
  WHERE closed IS FALSE
  GROUP BY id
) AS s USING (id)
LEFT OUTER JOIN (
  SELECT id, SUM(quantity)
  FROM tbl_purchase_item JOIN tbl_purchase USING ("order")
  WHERE closed IS FALSE
  GROUP BY id
) AS p USING (id)
ORDER BY id;

BTW, when providing example data, it's helpful if you post SQL
statements that create and populate the example tables.  That way
people can paste those statements into their own database and verify
their solutions against your data.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/