Skip site navigation (1) Skip section navigation (2)

Re: three-way join

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: three-way join
Date: 2004-04-19 17:45:51
Message-ID: 20040419174551.GA13762@wolff.to (view raw or flat)
Thread:
Lists: pgsql-sql
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
;

In response to

pgsql-sql by date

Next:From: Bruce MomjianDate: 2004-04-19 18:30:51
Subject: Re: Querying From two databases
Previous:From: Gary StainburnDate: 2004-04-19 16:13:22
Subject: Re: three-way join - solved

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group