Re: three-way join - solved - Mailing list 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