On Mon, Apr 19, 2004 at 13:47:28 +0100, Gary Stainburn <gary.stainburn@ringways.co.uk> wrote:
> Hi folks,
>
> here's a straight forward join that I simply can't get my head round.
>
> I've got
>
> consumables: cs_id, cs_make, cs_comments
> cons_locations: cl_id, cl_desc
> cons_stock: cs_id, cl_id, status (1=ordered, 2=in-stock)
>
> (one stock record per stock item, qty=3 means 3 records)
>
> I'm struggling to create a quiery to produce
>
> cs_id, cs_make, cs_comments, cl_desc, hand_qty, order_qty
>
> where hand_qty and order_qty is the number of records grouped by cs_id, cl_id,
> and status.
I think you can do something like:
SELECT a.cs_id, a.cs_make, a.cs_comments, b.cl_desc, c.hand_qty, d.order_qty FROM consumables a LEFT JOIN
cons_locationsb ON (a.cs_id = b.cs_id) LEFT JOIN (SELECT cs_id, cl_id, count(*) AS hand_qty FROM cons_stock
GROUPBY cs_id, cl_id WHERE status = 2) c ON (a.cs_id = c.cs_id and a.cl_id = c.cl_id) LEFT JOIN (SELECT
cs_id,cl_id, count(*) AS order_qty FROM cons_stock GROUP BY cs_id, cl_id WHERE status = 1) d ON (a.cs_id =
d.cs_idand a.cl_id = d.cl_id)
;
or like:
SELECT a.cs_id, a.cs_make, a.cs_comments, b.cl_desc, count(case c.status = 2 THEN 1 END) AS hand_qty, count(case
c.status= 1 THEN 1 END) AS order_qty FROM consumables a LEFT JOIN cons_locations b ON (a.cs_id = b.cs_id) LEFT JOIN
cons_stockc ON (a.cs_id = c.cs_id and a.cl_id = c.cl_id) GROUP BY a.cs_id, a.cs_make, a.cs_comments, b.cl_desc
;
