Joining three data sources.

Поиск
Список
Период
Сортировка
От Janning Vygen
Тема Joining three data sources.
Дата
Msg-id 200206191033.g5JAXnX19714@janning.planwerk6.local
обсуждение исходный текст
Ответы Re: Joining three data sources.  (Masaru Sugawara <rk73@sea.plala.or.jp>)
Список 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


В списке pgsql-sql по дате отправления:

Предыдущее
От: Oleg Bartunov
Дата:
Сообщение: Re: Help with a "recursive" query
Следующее
От: Marta Beatriz Caldentey
Дата:
Сообщение: Mirar y reenviar por favor!!! son 2 segundos