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
