Re: three-way join

Gary 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)
assuming that the PK's are:
consumables : cs_id
cons_loacations: cl_id
cons_stock: cs_id, cl_id
You could only have 1 record in cons_stock for each unique combination of consumable and location.
If the primary key for cons_stock would also include the field status you could have 2 records for each unique combination of consumable and location, one where status is ordered, and one where status is in-stock.

> 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.
Given the previous, the result for qty would be pretty obvious I think, since you would have only 1 record for the combination cs_id,cl_id and status.

>
> I've done the simple part and created a view balances to
> tally the cons_stock
> as:
>
> create view balances as
> select cost_cs_id, cost_cl_id, cost_css_id, count(*) as qty
> from cons_stock
> group by cost_cs_id, cost_cl_id, cost_css_id;
I don't understand where the cost_* fields come from, especially the cost_css_id field.
Assuming that these fields are the cs_id, cl_id and status qty is most likley going to be 1 all the time?
Maybe it's worth to rethink your database structure, or adding the qty fields to the table cons_stock and keeping them up-to-date?
(eg. CONS_STOCK (cs_id, cl_id, hand_qty, order_qty) PK(cs_id, cl_id) )
that way you simply change the quantity fields for each combination of location-consumable according to the situation (and sound the alarm if the reach a certain level?).

If anyone thinks I'm wrong, please correct me.

Regards,

Stijn Vanroye