X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id E187CD1C952
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 19 Apr 2004 14:41:59 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 28635-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 19 Apr 2004 14:41:58 -0300 (ADT)
Received: from wolff.to (wolff.to [66.93.249.74])
	by svr1.postgresql.org (Postfix) with SMTP id 71A2FD1B8DF
	for <pgsql-sql@postgresql.org>; Mon, 19 Apr 2004 14:41:56 -0300 (ADT)
Received: (qmail 14158 invoked by uid 500); 19 Apr 2004 17:45:51 -0000
Date: Mon, 19 Apr 2004 12:45:51 -0500
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
Message-ID: <20040419174551.GA13762@wolff.to>
Mail-Followup-To: Gary Stainburn <gary.stainburn@ringways.co.uk>,
	pgsql-sql@postgresql.org
References: <200404191347.28444.gary.stainburn@ringways.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200404191347.28444.gary.stainburn@ringways.co.uk>
User-Agent: Mutt/1.5.6i
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200404/178
X-Sequence-Number: 17553

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
;