Joining three data sources.
От | Janning Vygen |
---|---|
Тема | Joining three data sources. |
Дата | |
Msg-id | 200206191033.g5JAXnX19714@janning.planwerk6.local обсуждение исходный текст |
Ответы |
Re: Joining three data sources.
|
Список | 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
В списке pgsql-sql по дате отправления: