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