pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedJoining three data sources.
5+ messages / 3 participants
[nested] [flat]
* Joining three data sources.
@ 2002-06-19 10:33 Janning Vygen <vygen@gmx.de>
0 siblings, 1 reply; 5+ messages in thread
From: Janning Vygen @ 2002-06-19 10:33 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Joining three data sources.
@ 2002-06-19 14:09 Masaru Sugawara <rk73@sea.plala.or.jp>
parent: Janning Vygen <vygen@gmx.de>
0 siblings, 2 replies; 5+ messages in thread
From: Masaru Sugawara @ 2002-06-19 14:09 UTC (permalink / raw)
To: Janning Vygen <vygen@gmx.de>; +Cc: pgsql-sql
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
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;
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Joining three data sources.
@ 2002-06-19 18:07 Janning Vygen <janning@vygen.de>
parent: Masaru Sugawara <rk73@sea.plala.or.jp>
1 sibling, 0 replies; 5+ messages in thread
From: Janning Vygen @ 2002-06-19 18:07 UTC (permalink / raw)
To: Masaru Sugawara <rk73@sea.plala.or.jp>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Joining three data sources.
@ 2002-06-20 06:27 Janning Vygen <vygen@gmx.de>
parent: Masaru Sugawara <rk73@sea.plala.or.jp>
1 sibling, 1 reply; 5+ messages in thread
From: Janning Vygen @ 2002-06-20 06:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Joining three data sources.
@ 2002-06-20 13:55 Masaru Sugawara <rk73@sea.plala.or.jp>
parent: Janning Vygen <vygen@gmx.de>
0 siblings, 0 replies; 5+ messages in thread
From: Masaru Sugawara @ 2002-06-20 13:55 UTC (permalink / raw)
To: Janning Vygen <vygen@gmx.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-06-20 13:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-19 10:33 Joining three data sources. Janning Vygen <vygen@gmx.de>
2002-06-19 14:09 ` Masaru Sugawara <rk73@sea.plala.or.jp>
2002-06-19 18:07 ` Janning Vygen <janning@vygen.de>
2002-06-20 06:27 ` Janning Vygen <vygen@gmx.de>
2002-06-20 13:55 ` Masaru Sugawara <rk73@sea.plala.or.jp>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox