pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stijn Vanroye <s.vanroye@farcourier.com>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: three-way join - solved
Date: Tue, 20 Apr 2004 09:26:03 +0200
Message-ID: <71E201BE5E881C46811BA160694C5FCB046723@fs1000.farcourier.com> (raw)

First off, I'm sorry I couldn't get you a reply sooner. I see
you have found another way, which is good :-)

> I've sorted it.  
> 
> Firstly, I've done away with the status field.  If an item's 
> been issued or is 
> still on order it does not have a location.  I've therefore 
> set up two 
> locations, one of 'On Order' and one of 'Issued'. That's got 
> rid of one 
> table/relationship.

It's also possible to include a status field in the consumables
table, so you can give each consumable item it's own status directly.
That way you won't have to perform a join operation to get the status.
And/or you can filter directly on the consumables table, wich I think
can have a performance benefit (no join nescessary). Neighter do you 
need two location that aren't actually locations, which makes it easier
to generate a list of (real) locations if nescescary.
/*status field doesn't have to be a relation but a field which can only 
contain 3 values (your statusses)).*/ 
But hey, your solution is just as good, you have to look at the situation
at hand.

> 
> The relationship between the stock and the locations is 
> simple. Each stock 
> item has a location.  I therefore do a straight forward join 
> to end up with 
> stock+location information.
> 
> The Stock->Consumable relation is just as simple.  I do a 
> straight forward 
> join of the new stock+location data with the consumables 
> data, and end up 
> with what I need, consumable, location and quantity details, i.e. 
> 
> create view stock as
>    select c.*, b.cost_cl_id, b.cl_desc, b.qty from consumables c, 
>       (select b.*, cl.cl_desc 
>           from balances b, cons_locations cl 
>           where b.cost_cl_id = cl.cl_id 
>           order by cost_cs_id) b
>        where c.cs_id = b.cost_cs_id;
> 
> -- 
> Gary Stainburn

For what it's worth here's a query that I think might work fine in
you original situation, using your already created view:
select bmain.cost_cs_id, consumables.cs_make, consumables.cs_comments, 
       cons_locations.cl_desc, b1.qty as hand_qty, b2.qty as order_qty 
from 
(select cost_cs_id, cost_cl_id from balances group by cost_cl_id, cost_cs_id) as bmain
  left join (select qty, cost_cs_id, cost_cl_id from balances where cost_css_id=1) as b1 
    on (bmain.cost_cl_id=b1.cost_cl_id and bmain.cost_cs_id=b1.cost_cs_id)
  left join (select qty, cost_cs_id, cost_cl_id from balances where cost_css_id=2) as b2 
    on (bmain.cost_cl_id=b2.cost_cl_id and bmain.cost_cs_id=b2.cost_cs_id)
  left join consumables 
    on (bmain.cost_cs_id=consumables.cs_id)
  left join cons_locations 
    on (bmain.cost_cl_id=cons_locations.cl_id)

P.S. Don't ask about performance of the query, I haven't delved that deep into it :-)


Regards and good luck,

Stijn Vanroye



view thread (2+ messages)

Message-ID: <71E201BE5E881C46811BA160694C5FCB046723@fs1000.farcourier.com>
Permalink:  ../71E201BE5E881C46811BA160694C5FCB046723@fs1000.farcourier.com/
Also on:    postgresql.org/message-id/71E201BE5E881C46811BA160694C5FCB046723@fs1000.farcourier.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: s.vanroye@farcourier.com, gary.stainburn@ringways.co.uk
  Subject: Re: three-way join - solved
  In-Reply-To: <71E201BE5E881C46811BA160694C5FCB046723@fs1000.farcourier.com>
  References: <71E201BE5E881C46811BA160694C5FCB046723@fs1000.farcourier.com>

* 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