Re: three-way join

On Mon, Apr 19, 2004 at 13:47:28 +0100,
Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)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_locations b ON (a.cs_id = b.cs_id)
LEFT JOIN
(SELECT cs_id, cl_id, count(*) AS hand_qty FROM cons_stock
GROUP BY 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_id and 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_stock c 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
;