Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 664FA9FA311
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 11 Jan 2007 15:56:01 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 31710-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 11 Jan 2007 15:55:44 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175])
	by postgresql.org (Postfix) with ESMTP id 075EA9FA2EC
	for <pgsql-sql@postgresql.org>; Thu, 11 Jan 2007 15:55:45 -0400 (AST)
Received: by ug-out-1314.google.com with SMTP id k3so532272ugf
	for <pgsql-sql@postgresql.org>; Thu, 11 Jan 2007 11:55:41 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth;
	b=m2pTIX6cGryr9I1lwj8IFMu5qoNscTb/8hgKaNvGqh7oJ5BpHKtGSvZT+sD8Q4fEUSt0iO4C14AV/sZSiEWYBGs0WHyds6oHso5Ssmt3zJfos+IUh10vsJI9h9oyw8YHoleeF+HtS1AKNjpSrlrQbJ8Ma6jU6dOr67MBqqdf8fc=
Received: by 10.78.158.11 with SMTP id g11mr668242hue.1168545340714;
	Thu, 11 Jan 2007 11:55:40 -0800 (PST)
Received: by 10.78.57.4 with HTTP; Thu, 11 Jan 2007 11:55:40 -0800 (PST)
Message-ID: <bf05e51c0701111155l640194bble2c5c647db20cf93@mail.gmail.com>
Date: Thu, 11 Jan 2007 13:55:40 -0600
From: "Aaron Bono" <postgresql@aranya.com>
To: "Steve Sabljak" <sql-ss@sabljak.org>
Subject: Re: Aggregating both tables in a join?
Cc: pgsql-sql@postgresql.org
In-Reply-To: <81c28d920701110422y6217a3a8xa95276698b144096@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_23955_26329731.1168545340503"
References: <81c28d920701110422y6217a3a8xa95276698b144096@mail.gmail.com>
X-Google-Sender-Auth: a5bc6941acd1bfc7
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/33
X-Sequence-Number: 27185

------=_Part_23955_26329731.1168545340503
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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
==================================================================

------=_Part_23955_26329731.1168545340503
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 1/11/07, <b class="gmail_sendername">Steve Sabljak</b> &lt;<a href="mailto:sql-ss@sabljak.org">sql-ss@sabljak.org</a>&gt; wrote:<div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br><br>select cf.flight_id, tl1.num_legs, cf.cmp_flight_id, tl2.num_legs cmp_num_legs<br>from cmp_flight cf<br>&nbsp;&nbsp;&nbsp;&nbsp;join (select fl1.flight_id fid1, count(*) num_legs<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from flight_leg fl1 group by fl1.flight_id) tl1 on fid1 = 
cf.flight_id<br>&nbsp;&nbsp;&nbsp;&nbsp;join (select fl2.flight_id fid2, count(*) num_legs<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from flight_leg fl2 group by fl2.flight_id) tl2 on fid2 =<br>cf.cmp_flight_id;<br><br>Is this a better way to go about it?<br>I&#39;ve considered keeping a track of the number of legs in the flight
<br>table, using a trigger, but it doesn&#39;t seem relationally &#39;clean&#39;.<br><br></blockquote></div><br>You can try this but I cannot say which would perform better.&nbsp; I have heard many times that &quot;distinct&quot; can be a performance killer.
<br><br>select <br>&nbsp;&nbsp;&nbsp; cf.flight_id, <br>&nbsp;&nbsp;&nbsp; count(distinct(fl1.leg_id)) as num_legs, <br>&nbsp;&nbsp;&nbsp; cf.cmp_flight_id, <br>&nbsp;&nbsp;&nbsp; count(distinct(fl2.leg_id)) cmp_num_legs<br>from cmp_flight cf<br>join flight_leg fl1 on fl1.flight_id
 = cf.flight_id<br>join flight_leg fl2 on fl2.flight_id = cf.cmp_flight_id<br>group by<br>&nbsp;&nbsp;&nbsp; cf.flight_id,<br>&nbsp;&nbsp;&nbsp; cf.cmp_flight_id<br>;<br><br>This can give different results if you don&#39;t have a unique constraint (or primary key set to) cmp_flight.flight_id, cmp_flight.cmp_flight_id .
<br clear="all"><br>-- <br>==================================================================<br>&nbsp;&nbsp; Aaron Bono<br>&nbsp;&nbsp; Aranya Software Technologies, Inc.<br>&nbsp;&nbsp; <a href="http://www.aranya.com">http://www.aranya.com</a><br>&nbsp;&nbsp; 
<a href="http://codeelixir.com">http://codeelixir.com</a><br>==================================================================

------=_Part_23955_26329731.1168545340503--