pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAggregating both tables in a join?
2+ messages / 2 participants
[nested] [flat]
* Aggregating both tables in a join?
@ 2007-01-11 12:22 Steve Sabljak <sql-ss@sabljak.org>
2007-01-11 19:55 ` Re: Aggregating both tables in a join? Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 2+ messages in thread
From: Steve Sabljak @ 2007-01-11 12:22 UTC (permalink / raw)
To: pgsql-sql
Hi,
I've got a table which models the relationship between flights and
legs, which is defined like this:-
create table flight_leg (
flight_id integer references flight(id),
leg_id integer references leg(id)
);
One leg may be part of many flights and one flight may be made up of 1
or more legs.
I also have a table which maps flights to comparable flights. The
mapping is created manually. It is defined like this:-
create table cmp_flight (
flight_id integer references flight(id),
cmp_flight_id integer references flight(id)
);
The flight and leg tables referenced are omitted for brevity.
I want to be able to show all the flight_ids and their leg counts as
well as the cmp_flight_ids and leg counts.
e.g.
insert into flight_legs values (1, 1);
insert into flight_legs values (1, 2);
insert into flight_legs values (2, 3);
insert into flight_legs values (2, 4);
insert into flight_legs values (2, 5);
insert into flight_legs values (3, 3);
insert into flight_legs values (4, 1);
insert into flight_legs values (5, 3);
insert into flight_legs values (5, 4);
insert into flight_legs values (6, 4);
insert into flight_legs values (7, 5);
insert into cmp_flight values (1, 2);
insert into cmp_flight values (1, 3);
insert into cmp_flight values (4, 5);
insert into cmp_flight values (4, 6);
insert into cmp_flight values (4, 7);
The result I'm looking for is
flight_id num_legs cmp_flight_id cmp_num_legs
1 2 2 3
1 2 3 1
4 1 5 2
4 1 6 1
4 1 7 1
This does the trick:-
select cf.flight_id, tl1.num_legs, cf.cmp_flight_id, tl2.num_legs cmp_num_legs
from cmp_flight cf
join (select fl1.flight_id fid1, count(*) num_legs
from flight_leg fl1 group by fl1.flight_id) tl1 on fid1 = cf.flight_id
join (select fl2.flight_id fid2, count(*) num_legs
from flight_leg fl2 group by fl2.flight_id) tl2 on fid2 =
cf.cmp_flight_id;
Is this a better way to go about it?
I've considered keeping a track of the number of legs in the flight
table, using a trigger, but it doesn't seem relationally 'clean'.
Kind Regards,
Steve Sabljak
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Aggregating both tables in a join?
2007-01-11 12:22 Aggregating both tables in a join? Steve Sabljak <sql-ss@sabljak.org>
@ 2007-01-11 19:55 ` Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 2+ messages in thread
From: Aaron Bono @ 2007-01-11 19:55 UTC (permalink / raw)
To: Steve Sabljak <sql-ss@sabljak.org>; +Cc: pgsql-sql
On 1/11/07, Steve Sabljak <sql-ss@sabljak.org> wrote:
>
>
>
> select cf.flight_id, tl1.num_legs, cf.cmp_flight_id, tl2.num_legscmp_num_legs
> from cmp_flight cf
> join (select fl1.flight_id fid1, count(*) num_legs
> from flight_leg fl1 group by fl1.flight_id) tl1 on fid1 =
> cf.flight_id
> join (select fl2.flight_id fid2, count(*) num_legs
> from flight_leg fl2 group by fl2.flight_id) tl2 on fid2 =
> cf.cmp_flight_id;
>
> Is this a better way to go about it?
> I've considered keeping a track of the number of legs in the flight
> table, using a trigger, but it doesn't seem relationally 'clean'.
>
>
You can try this but I cannot say which would perform better. I have heard
many times that "distinct" can be a performance killer.
select
cf.flight_id,
count(distinct(fl1.leg_id)) as num_legs,
cf.cmp_flight_id,
count(distinct(fl2.leg_id)) cmp_num_legs
from cmp_flight cf
join flight_leg fl1 on fl1.flight_id = cf.flight_id
join flight_leg fl2 on fl2.flight_id = cf.cmp_flight_id
group by
cf.flight_id,
cf.cmp_flight_id
;
This can give different results if you don't have a unique constraint (or
primary key set to) cmp_flight.flight_id, cmp_flight.cmp_flight_id .
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-01-11 19:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-11 12:22 Aggregating both tables in a join? Steve Sabljak <sql-ss@sabljak.org>
2007-01-11 19:55 ` Aaron Bono <postgresql@aranya.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