if exists select help 1

[jgurgen]

i would like my data to show a 1 if PlayerID exists in GameRoster and a 0 if it doesn't

my currently working script returns null if the playerid doesnt exist.

Select TeamRoster.TeamID, TeamRoster.PlayerID, TeamRoster.PlayerNumber, (Select PlayerID from GameRoster WHERE PlayerID = TeamRoster.PlayerID) as PlayerExists
FROM TeamRoster
INNER JOIN Game ON TeamRoster.TeamID=Game.A_TeamID
LEFT JOIN GameRoster ON TeamRoster.TeamID=GameRoster.TeamID
WHERE Game.A_TeamID = 24 AND Game.GameID=1

using the if exists it crashes.

Select TeamRoster.TeamID, TeamRoster.PlayerID, TeamRoster.PlayerNumber, (if(exists(Select PlayerID from GameRoster WHERE PlayerID = TeamRoster.PlayerID)) else(0)) as PlayerExists
FROM TeamRoster
INNER JOIN Game ON TeamRoster.TeamID=Game.A_TeamID
LEFT JOIN GameRoster ON TeamRoster.TeamID=GameRoster.TeamID
WHERE Game.A_TeamID = 24 AND Game.GameID=1

 

[r937]

SELECT TeamRoster.TeamID
     , TeamRoster.PlayerID
     , TeamRoster.PlayerNumber
     , CASE WHEN GameRoster.PlayerID IS NULL
            THEN 0 ELSE 1 END AS PlayerExists
  FROM Game
INNER 
  JOIN TeamRoster  
    ON TeamRoster.TeamID = Game.A_TeamID
LEFT OUTER
  JOIN GameRoster 
    ON GameRoster.TeamID = TeamRoster.TeamID
   AND GameRoster.PlayerID = = TeamRoster.PlayerID
 WHERE Game.A_TeamID = 24 
   AND Game.GameID = 1

r937.com | rudy.ca