From: Janning Vygen <vygen@gmx.de>
To: pgsql-sql@postgresql.org
Subject: Joining three data sources.
Date: Wed, 19 Jun 2002 12:33:47 +0200
Message-ID: <200206191033.g5JAXnX19714@janning.planwerk6.local> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: vygen@gmx.de
Subject: Re: Joining three data sources.
In-Reply-To: <200206191033.g5JAXnX19714@janning.planwerk6.local>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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