multiple join SQL query

[Yorgos]

Hi!

I have three tables of hockeystats

players seasons goaliestats
___________ _____________ ______________
|*id |- |*id |- |*id |
|pp | |->|pid | |->|sid |
|firstname | |games | |saves |
|lastname | |goals | |wins |
|___________| |assists | |losses |
|_____________| |______________ |

I have this query that doesn't work.

SELECT First(p.firstna me), First(p.lastnam e), Sum(s.games),
Sum(g.saves)
FROM players AS p
INNER JOIN seasons AS s ON p.id = s.pid
LEFT JOIN goaliestats AS g ON s.id = g.sid
WHERE p.pp = 'G'
GROUP BY p.id
ORDER BY Sum(s.games) DESC

data in "seasons" table is mandatory and there are several rows per id
in "players" table. Table goaliestats is kind of extra info for
"seasons" table data and there is one or none rows per id in "seasons"
table.

I would like to get summaries from "seasons" and "goaliestat s" grouped
by players.id. If I have to change the table structure I wan't to do
this only for "goaliestat s" table. Can anyone help me, please?

thanks,
Yorgos

[henrikb]

SELECT Sum(s.games), Sum(g.saves), First(p.firstna me),
First(p.lastnam e)
FROM (seasons AS s LEFT JOIN players AS p ON s.pid = p.id)
LEFT JOIN goaliestats AS g ON s.id = g.sid
WHERE p.pp = 'G'
GROUP BY p.id
ORDER BY Sum(s.games) DESC

did the trick!