From: Aaron Bono <postgresql@aranya.com>
To: Steve Sabljak <sql-ss@sabljak.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Aggregating both tables in a join?
Date: Thu, 11 Jan 2007 13:55:40 -0600
Message-ID: <bf05e51c0701111155l640194bble2c5c647db20cf93@mail.gmail.com> (raw)
In-Reply-To: <81c28d920701110422y6217a3a8xa95276698b144096@mail.gmail.com>
References: <81c28d920701110422y6217a3a8xa95276698b144096@mail.gmail.com>
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.comhttp://codeelixir.com
==================================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@aranya.com, sql-ss@sabljak.org
Subject: Re: Aggregating both tables in a join?
In-Reply-To: <bf05e51c0701111155l640194bble2c5c647db20cf93@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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