Re: three-way join - Mailing list pgsql-sql

On Monday 19 April 2004 3:06 pm, Stijn Vanroye wrote:
> 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.

Sorry for the confusion.  For the purpose of simplicity I trimmed the info - a 
little too far it seems.

cons_stock has as it's PK a serial field, cost_id (cost_ is the prefix I use 
for fields on this table, the other fields therefore are cost_cs_id and 
cost_cl_id).  I need to be able to track individual items, and thus give it a 
unique id.  If I ordered 4 HP 4100 toners, they'd create 4 seperate records 
even though the cost_cs_id and cost_cl_id's would all be the same.

>
> > 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?).

the cost_ (abrev of cons_stock) is the prefix of the fields on the cons_stock 
field. consumables have prefix cs_ and locations have cl_. Therefore when 
cons_stock references consumables id field it is called cost_cd_id.

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

I hope my my explanation's cleared up the grey area.  I've included all of the 
relevent schema below to help show what I want.

create table cons_types ( cst_id        serial not null unique, cst_desc    varchar(40), primary key (cst_id)
);
insert into cons_types (cst_desc) values ('Toner cartridge');    -- 1
insert into cons_types (cst_desc) values ('Ink cartridge');    -- 2;

create table consumables ( cs_id        serial not null unique, cs_make    varchar(40), cs_code    varchar(20), cs_type
  int4 references cons_types(cst_id) not null, cs_colour    varchar(40), cs_comments    text, primary key (cs_id)
 
);

insert into consumables (cs_make, cs_code, cs_type,cs_colour, cs_supp, 
cs_comments) values ('HP', 'C4096A', 1, 'BLACK', 5, '2100    2    0');

create table cons_locations ( cl_id        serial not null unique, cl_desc    varchar(40), primary key (cl_id)
);
insert into cons_locations (cl_desc) values ('Leeds Computer Room');    -- 1

create table cons_status ( css_id        serial not null unique, css_desc    varchar(40), primary key (css_id)
);
insert into cons_status (css_desc) values ('Ordered');    -- 1
insert into cons_status (css_desc) values ('In Stock');    -- 2
insert into cons_status (css_desc) values ('Issued');    -- 3

create table cons_stock ( cost_id    serial not null unique, cost_cs_id    int4 references consumables(cs_id) not null,
cost_css_id   int4 references cons_status(css_id) not null, cost_cl_id    int4 references cons_locations(cl_id) not
null,cost_supp    int4 references contacts(co_id), cost_comments    text, primary key (cost_id)
 
);
-- insert 2 HP 2100 toners in stock at Leeds
insert into cons_stock (cost_cs_id, cost_css_id, cost_cl_id) values (1, 2, 1); 
insert into cons_stock (cost_cs_id, cost_css_id, cost_cl_id) values (1, 2, 1);

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;
 


>
>
> Regards,
>
> Stijn Vanroye
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000