pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedthree-way join
4+ messages / 3 participants
[nested] [flat]
* three-way join
@ 2004-04-19 12:47 Gary Stainburn <gary.stainburn@ringways.co.uk>
2004-04-19 17:45 ` Re: three-way join Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 4+ messages in thread
From: Gary Stainburn @ 2004-04-19 12:47 UTC (permalink / raw)
To: pgsql-sql
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)
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.
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 then have trouble joining this to the consumables and locations table to get
the results I need.
--
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] 4+ messages in thread
* Re: three-way join
2004-04-19 12:47 three-way join Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2004-04-19 17:45 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2004-04-19 17:45 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
On Mon, Apr 19, 2004 at 13:47:28 +0100,
Gary Stainburn <gary.stainburn@ringways.co.uk> 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)
>
> 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.
I think you can do something like:
SELECT a.cs_id, a.cs_make, a.cs_comments, b.cl_desc, c.hand_qty, d.order_qty
FROM consumables a LEFT JOIN cons_locations b ON (a.cs_id = b.cs_id)
LEFT JOIN
(SELECT cs_id, cl_id, count(*) AS hand_qty FROM cons_stock
GROUP BY cs_id, cl_id WHERE status = 2) c
ON (a.cs_id = c.cs_id and a.cl_id = c.cl_id)
LEFT JOIN
(SELECT cs_id, cl_id, count(*) AS order_qty FROM cons_stock
GROUP BY cs_id, cl_id WHERE status = 1) d
ON (a.cs_id = d.cs_id and a.cl_id = d.cl_id)
;
or like:
SELECT a.cs_id, a.cs_make, a.cs_comments, b.cl_desc,
count(case c.status = 2 THEN 1 END) AS hand_qty,
count(case c.status = 1 THEN 1 END) AS order_qty
FROM consumables a LEFT JOIN cons_locations b ON (a.cs_id = b.cs_id)
LEFT JOIN cons_stock c ON (a.cs_id = c.cs_id and a.cl_id = c.cl_id)
GROUP BY a.cs_id, a.cs_make, a.cs_comments, b.cl_desc
;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: three-way join
@ 2004-04-19 14:06 Stijn Vanroye <s.vanroye@farcourier.com>
2004-04-19 15:01 ` Re: three-way join Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 4+ messages in thread
From: Stijn Vanroye @ 2004-04-19 14:06 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: three-way join
2004-04-19 14:06 Re: three-way join Stijn Vanroye <s.vanroye@farcourier.com>
@ 2004-04-19 15:01 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 4+ messages in thread
From: Gary Stainburn @ 2004-04-19 15:01 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-04-19 17:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-19 12:47 three-way join Gary Stainburn <gary.stainburn@ringways.co.uk>
2004-04-19 17:45 ` Bruno Wolff III <bruno@wolff.to>
2004-04-19 14:06 Re: three-way join Stijn Vanroye <s.vanroye@farcourier.com>
2004-04-19 15:01 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
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