Message-Id: <200206200627.g5K6R8X31442@janning.planwerk6.local>
Content-Type: text/plain;
  charset="iso-8859-1"
From: Janning Vygen <vygen@gmx.de>
Organization: Planwerk 6 /websolutions
To: pgsql-sql@postgresql.org
Subject: Re: Joining three data sources.
Date: Thu, 20 Jun 2002 08:27:07 +0200
References: <200206191033.g5JAXnX19714@janning.planwerk6.local>
	<20020619230842.91E2.RK73@sea.plala.or.jp>
In-Reply-To: <20020619230842.91E2.RK73@sea.plala.or.jp>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit

Am Mittwoch, 19. Juni 2002 16:09 schrieb Masaru Sugawara:
> On Wed, 19 Jun 2002 12:33:47 +0200
>
> Janning Vygen <vygen@gmx.de> wrote:
> > ---------------------
> > Result Inter Mailand vs. AC ROM 2:1
> > team1_id|team2_id|goals1|goals2
> >   1         2       2      1
>
> SELECT go1.game_id, go1.team1_id, go1.team2_id,
>           SUM(CASE WHEN go2.team_id = go1.team1_id
>                      THEN go2.n ELSE 0 END) AS goals1,
>           SUM(CASE WHEN go2.team_id = go1.team2_id
>                      THEN go2.n ELSE 0 END) AS goals2
> FROM  (SELECT game_id,
>                     min(team_id) AS team1_id,
>                     max(team_id) AS team2_id
>               FROM goal
>             GROUP BY 1) AS go1,
>           (SELECT game_id, team_id, count(*) AS n
>                FROM goal
>             GROUP BY 1, 2) AS go2
> WHERE go1.game_id = go2.game_id
> GROUP BY 1, 2, 3;

Oh thanks  a lot. You pushed me in the right direction. i still get 
headache when trying to write complicated selects. there was 
something wrong in your statement but i was able to correct it by 
myself. Thanks for your help!!

Are you able to type those queries in minutes?? It seems so ... 
amazing! 

> As for Goal table, if it has a large number of the rows, you maybe
> need to create a unique index on it.

of course. it was just an example...

kind regards 
janning