Обсуждение: Joining three data sources.
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
On Wed, 19 Jun 2002 12:33:47 +0200 Janning Vygen <vygen@gmx.de> wrote: > --------------------- > 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 How about something like this: SELECT go1.game_id, go1.team1_id, go1.team2_id, SUM(CASE WHEN go2.team_id = go1.team1_id THENgo2.n ELSE 0 END) AS goals1, SUM(CASE WHEN go2.team_id = go1.team2_id THEN go2.n ELSE 0 END)AS goals2 FROM (SELECT game_id, min(team_id) AS team1_id, max(team_id) AS team2_id FROM goal GROUP BY 1) AS go1, (SELECT game_id, team_id, count(*) AS n FROM goal GROUP BY 1, 2) AS go2 WHERE go1.game_id = go2.game_id GROUP BY 1, 2, 3; P.S. As for Goal table, if it has a large number of the rows, you maybe need to create a unique index on it. CREATE UNIQUE INDEX idx_goal ON goal(game_id, team_id, minute); Regards, Masaru Sugawara
Am Mittwoch, 19. Juni 2002 16:09 schrieb Masaru Sugawara: > On Wed, 19 Jun 2002 12:33:47 +0200 > > Janning Vygen <vygen@gmx.de> wrote: > > --------------------- > > Result Inter Mailand vs. AC ROM 2:1 > > team1_id|team2_id|goals1|goals2 > > 1 2 2 1 > > SELECT go1.game_id, go1.team1_id, go1.team2_id, > SUM(CASE WHEN go2.team_id = go1.team1_id > THEN go2.n ELSE 0 END) AS goals1, > SUM(CASE WHEN go2.team_id = go1.team2_id > THEN go2.n ELSE 0 END) AS goals2 > FROM (SELECT game_id, > min(team_id) AS team1_id, > max(team_id) AS team2_id > FROM goal > GROUP BY 1) AS go1, > (SELECT game_id, team_id, count(*) AS n > FROM goal > GROUP BY 1, 2) AS go2 > WHERE go1.game_id = go2.game_id > GROUP BY 1, 2, 3; Oh thanks a lot. You pushed me in the right direction. i still get headache when trying to write complicated selects. there was something wrong in your statement but i was able to correct it by myself. Thanks for your help!! Are you able to type those queries in minutes?? It seems so ... amazing! > As for Goal table, if it has a large number of the rows, you maybe > need to create a unique index on it. of course. it was just an example... kind regards janning
On Thu, 20 Jun 2002 08:27:07 +0200 Janning Vygen <vygen@gmx.de> wrote: > Oh thanks a lot. You pushed me in the right direction. i still get headache > when trying to write complicated selects. there was something wrong > in your statement Oh! I slipped up. You are exactly right. > but i was able to correct it by myself. Thanks for your help!! > Are you able to type those queries in minutes?? It seems so ... amazing! No. Actually, I may have spent a few hours before I was aware of making use of Max/Min aggregations to divide the values of the team_id into the team1_id or tema2_id. > > > As for Goal table, if it has a large number of the rows, you maybe > > need to create a unique index on it. > > of course. it was just an example... > > kind regards > janning Regards, Masaru Sugawara
Am Mittwoch, 19. Juni 2002 16:09 schrieb Masaru Sugawara: > On Wed, 19 Jun 2002 12:33:47 +0200 > > Janning Vygen <vygen@gmx.de> wrote: > > --------------------- > > Result Inter Mailand vs. AC ROM 2:1 > > team1_id|team2_id|goals1|goals2 > > 1 2 2 1 > > SELECT go1.game_id, go1.team1_id, go1.team2_id, > SUM(CASE WHEN go2.team_id = go1.team1_id > THEN go2.n ELSE 0 END) AS goals1, > SUM(CASE WHEN go2.team_id = go1.team2_id > THEN go2.n ELSE 0 END) AS goals2 > FROM (SELECT game_id, > min(team_id) AS team1_id, > max(team_id) AS team2_id > FROM goal > GROUP BY 1) AS go1, > (SELECT game_id, team_id, count(*) AS n > FROM goal > GROUP BY 1, 2) AS go2 > WHERE go1.game_id = go2.game_id > GROUP BY 1, 2, 3; Oh thanks a lot. You pushed me in the right direction. i still get headache when trying to write complicated selects. there was something wrong in your statement but i was able to correct it by myself. Thanks for your help!! Are you able to type those queries in minutes?? It seems so ... amazing! > As for Goal table, if it has a large number of the rows, you maybe > need to create a unique index on it. of course. it was just an example... kind regards janning