From: Bruno Wolff III <bruno@wolff.to>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: three-way join
Date: Mon, 19 Apr 2004 12:45:51 -0500
Message-ID: <20040419174551.GA13762@wolff.to> (raw)
In-Reply-To: <200404191347.28444.gary.stainburn@ringways.co.uk>
References: <200404191347.28444.gary.stainburn@ringways.co.uk>
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_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
;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, gary.stainburn@ringways.co.uk
Subject: Re: three-way join
In-Reply-To: <20040419174551.GA13762@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox