Thread: 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