I would like to execute a single Select statement that returns correct results for missing records of a Left Outer Join.
ISNULL(R.ID,'MISSING') as 'Record Status',
L.ID as 'Left ID',
R.ID as 'Right ID',
WHEN R.ID ISNULL THEN 'Missing'
End as 'Case Result'
From LeftTable L
LEFT OUTER JOIN RightTable R ON L.ID = R.ID
The Case Statement does not function correctly, because R.ID does not exist for every L.ID. While the Results Pane will show R.ID = 'NULL', it is Missing rather than NULL. so 'Case Result' = 'Available' when it really is not available.
Is there a method to use inside a Case Statement to evaluate when a record is missing from a joined table?