Joining three data sources.

From: Janning Vygen <vygen(at)gmx(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: Joining three data sources.
Date: 2002-06-19 10:33:47
Message-ID: 200206191033.g5JAXnX19714@janning.planwerk6.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Marta Beatriz Caldentey 2002-06-19 11:05:16 Mirar y reenviar por favor!!! son 2 segundos
Previous Message Christoph Haller 2002-06-19 10:18:38 Re: Aggregates not allowed in WHERE clause?