From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: three-way join
Date: Mon, 19 Apr 2004 13:47:28 +0100
Message-ID: <200404191347.28444.gary.stainburn@ringways.co.uk> (raw)
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
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: gary.stainburn@ringways.co.uk
Subject: Re: three-way join
In-Reply-To: <200404191347.28444.gary.stainburn@ringways.co.uk>
* 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