Thread: Joining three data sources.

Joining three data sources.

From
Janning Vygen
Date:
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


Re: Joining three data sources.

From
Masaru Sugawara
Date:
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




Re: Joining three data sources.

From
Janning Vygen
Date:
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


Re: Joining three data sources.

From
Masaru Sugawara
Date:
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




Re: Joining three data sources.

From
Janning Vygen
Date:
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