Date: Wed, 19 Jun 2002 23:09:02 +0900
From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: Janning Vygen <vygen@gmx.de>
Subject: Re: Joining three data sources.
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200206191033.g5JAXnX19714@janning.planwerk6.local>
References: <200206191033.g5JAXnX19714@janning.planwerk6.local>
Errors-To: rk73@sea.plala.or.jp
Message-Id: <20020619230842.91E2.RK73@sea.plala.or.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit

On Wed, 19 Jun 2002 12:33:47 +0200
Janning Vygen <vygen@gmx.de> wrote:

> ---------------------
> Result Inter Mailand vs. AC ROM 2:1
> 
> How can i select all games with their results in a tabel like this:
> (i dont care about the team names. this is easy to achieve of course, 
> my question is just about the goals)
> 
> team1_id|team2_id|goals1|goals2
>   1         2       2      1


 How about something like this:

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;


P.S.

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

CREATE UNIQUE INDEX idx_goal ON goal(game_id, team_id, minute);


Regards,
Masaru Sugawara