Re: three-way join - solved

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