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

Re: three-way join - solved

From: "Stijn Vanroye" <s(dot)vanroye(at)farcourier(dot)com>
To: "Gary Stainburn" <gary(dot)stainburn(at)ringways(dot)co(dot)uk>,<pgsql-sql(at)postgresql(dot)org>
Subject: Re: three-way join - solved
Date: 2004-04-20 07:26:03
Message-ID: 71E201BE5E881C46811BA160694C5FCB046723@fs1000.farcourier.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

pgsql-sql by date

Next:From: Martin MarquesDate: 2004-04-20 14:40:36
Subject: Re: Can someone tell me why this statement is failing?
Previous:From: Oliver JowettDate: 2004-04-20 02:57:53
Subject: Re: Prepared Statements and large where-id-in constant blocks?

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