Message-Id: <200206191033.g5JAXnX19714@janning.planwerk6.local>
Content-Type: text/plain;
  charset="iso-8859-15"
From: Janning Vygen <vygen@gmx.de>
Organization: Planwerk 6 /websolutions
To: pgsql-sql@postgresql.org
Subject: Joining three data sources.
Date: Wed, 19 Jun 2002 12:33:47 +0200
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit

hi,

i tried the whole night to get this work. now i really need your help.
I hope my question is understandable. i tried my best and deliver 
tested sql code for you to make it easier for you. 

i would like to model football games like this:

--- tested sql code ----

CREATE TABLE Team (
   id serial,
   name text
);

CREATE TABLE Game (
  id       serial,
  team1_id int4 REFERENCES Team (id),
  team2_id int4 REFERENCES Team (id),
  kickoff  timestamp
);

CREATE TABLE Goal (
  game_id int4 REFERENCES Game (id),
  team_id int4 REFERENCES Team (id),
  minute  int2
);

INSERT INTO Team (name) VALUES ('Inter Mailand');
INSERT INTO Team (name) VALUES ('AC Rom');
INSERT INTO Game (team1_id, team2_id, kickoff) VALUES (1,2, 'now');
INSERT INTO Goal VALUES (1,2,10);
INSERT INTO Goal VALUES (1,1,25);
INSERT INTO Goal VALUES (1,1,75);

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

I made it already to get all goals counted made by team 1 with a 
select like this:

--- tested sql code ---
SELECT team1_id, team2_id, count(team_id) AS goals1
FROM  game INNER JOIN goal 
      ON (goal.game_id = game.id AND 
          goal.team_id=game.team1_id)
GROUP BY team1_id, team2_id;
------

i can select all goals by team2 with a very similar select of course, 
but how can i make a join with three tables showing me the results of 
the games?? 

I tried a lot of things but they just dont work.
I am not sure if it can be made with just one select.
any help is very appreciated.

kind regards
janning