Thread: Aggregating both tables in a join?
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 groupby 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
On 1/11/07, Steve Sabljak <sql-ss@sabljak.org> wrote:
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
==================================================================
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'.
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
==================================================================