DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:sender:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition:x-google-sender-auth;
	b=DcjiWzAb2zLeGd2pYxoWHuS/5faOShhgPvs+Bz5L25xfeacKMxb2kwQzMzfSD8SkiLFH4R/AV+dMybjaZ+9X+N2aiKhIsrx67q6dXUWH+QMLrYw14jy1q8rsWgfOYrl9CNxj4EsYAiX8d6iSz+P+PoOJ41DGhpcNwmRx7ALcLUo=
Message-ID: <81c28d920701110422y6217a3a8xa95276698b144096@mail.gmail.com>
Date: Thu, 11 Jan 2007 23:22:21 +1100
From: "Steve Sabljak" <sql-ss@sabljak.org>
To: pgsql-sql@postgresql.org
Subject: Aggregating both tables in a join?
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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