pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: three-way join - solved
2+ messages / 2 participants
[nested] [flat]
* Re: three-way join - solved
@ 2004-04-19 16:13 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 2+ messages in thread
From: Gary Stainburn @ 2004-04-19 16:13 UTC (permalink / raw)
To: pgsql-sql
On Monday 19 April 2004 4:01 pm, Gary Stainburn wrote:
> 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
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.
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
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: three-way join - solved
@ 2004-04-20 07:26 Stijn Vanroye <s.vanroye@farcourier.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stijn Vanroye @ 2004-04-20 07:26 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-04-20 07:26 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-19 16:13 Re: three-way join - solved Gary Stainburn <gary.stainburn@ringways.co.uk>
2004-04-20 07:26 Re: three-way join - solved Stijn Vanroye <s.vanroye@farcourier.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox