SQL Join Error

[VBNewfie]

This SQL statement generates the following error when used on an Access 2000 DB....

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'POOL_TEAM_PICKS.PLAYER_ID = POOL_PLAYERS.PLAYER_ID RIGHT OUTER JOIN POOL_TEAMS ON POOL_TEAM_PICKS.TEAM_ID = POOL_TEAMS.TEAM_ID'.

/hockey/pool/stats1.asp, line 19



Here is the SQL statement....

RS.Open "SELECT SUM(POOL_PLAYERS.POINTS) AS PointTotal, SUM(POOL_PLAYERS.GOALS) AS GoalTotal, SUM(POOL_PLAYERS.ASSISTS) as AssistTotal, POOL_TEAMS.TEAM_LNAME, POOL_TEAMS.TEAM_FNAME, POOL_TEAMS.TEAM_ID, POOL_TEAMS.TEAM_NAME FROM POOL_TEAM_PICKS INNER JOIN POOL_PLAYERS ON POOL_TEAM_PICKS.PLAYER_ID = POOL_PLAYERS.PLAYER_ID RIGHT OUTER JOIN POOL_TEAMS ON POOL_TEAM_PICKS.TEAM_ID = POOL_TEAMS.TEAM_ID GROUP BY POOL_TEAMS.TEAM_ID, POOL_TEAMS.TEAM_NAME, POOL_TEAMS.TEAM_LNAME, POOL_TEAMS.TEAM_FNAME;",Con,3



Thanks in advance,

Brian

 

[dalchri]

If I'm not mistaken, Jet SQL doesn't use or support the word OUTER in a RIGHT OUTER JOIN. Try the SQL statement using just the join phrase RIGHT JOIN.

 

[Mighty]

Or try adding parentheses around the first join:


.......FROM (POOL_TEAM_PICKS INNER JOIN POOL_PLAYERS ON POOL_TEAM_PICKS.PLAYER_ID = POOL_PLAYERS.PLAYER_ID) RIGHT OUTER JOIN POOL_TEAMS ON POOL_TEAM_PICKS.TEAM_ID = POOL_TEAMS.TEAM_ID GROUP BY POOL_TEAMS.TEAM_ID, POOL_TEAMS.TEAM_NAME, POOL_TEAMS.TEAM_LNAME, POOL_TEAMS.TEAM_FNAME;",Con,3
Mighty