Left Join Problem

[zinkjo]

I have 2 Tables: SeasonPlayer and GamePlayerStats.
I want to retrieve all of the info from SeasonPlayer and if their is matching info on GamePlayerStats(for a particular game) retrieve that also.

I think this is the correct query, but I keep getting "Join Expression Not Supported". Please help !!!

SELECT lastNm
FROM SeasonPlayer LEFT JOIN GamePlayerStats ON SeasonPlayer.playerid = GamePlayerStats.playerid and GamePlayerStats.gameid = '12345'

 

[scriverb]

Try this SQL:

SELECT SeasonPlayer.lastNm
FROM SeasonPlayer LEFT JOIN GamePlayerStats ON SeasonPlayer.playerid = GamePlayerStats.playerid
WHERE GamePlayerStats.gameid = '12345';

You have to seperate the Join criteria from the record Selection criteria with a WHERE clause statement.

Bob Scriver

 

[zinkjo]

I tried that and it gets me past the syntax error, but I don't get the desired results. The where clause filters out all results. I always want the rows returned from the seasonPlayer table even if there is no match on the gamePlayerStats table.

 

[scriverb]

This requires two queries. Save this query as qryRecSelect:
Select * FROM GamePlayerStats WHERE GamePlayerStats.gameid = '12345';

This first one performs the specific select of the records from GamePlayerStats. The next query uses this query as the right side of the Left Join.
SELECT SeasonPlayer.*, qryRecSelect.*
FROM SeasonPlayer LEFT JOIN qryRecSelect ON SeasonPlayer.playerid = qryRecSelect.playerid;

To execute this just run the final query.


Bob Scriver

 

[swampBoogie]

zinkjo:

Your SQL is correct, it is just Access that have some limitations as to what it allows.

SELECT lastNm  
   FROM SeasonPlayer LEFT OUTER JOIN GamePlayerStats 
     ON (SeasonPlayer.playerid = GamePlayerStats.playerid 
    and GamePlayerStats.gameid = '12345')

Putting a condition on the right hand table in the where clause will not be a good idea as it will countereffect the outer join.