Content-Type: text/plain;
  charset="us-ascii"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: three-way join
Date: Mon, 19 Apr 2004 13:47:28 +0100
User-Agent: KMail/1.4.3
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200404191347.28444.gary.stainburn@ringways.co.uk>

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've done the simple part and created a view balances to tally the cons_stock 
as:

create view balances as 
  select cost_cs_id, cost_cl_id, cost_css_id, count(*) as qty 
    from cons_stock 
    group by cost_cs_id, cost_cl_id, cost_css_id;

I then have trouble joining this to the consumables and locations table to get 
the results I need.
-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000