Using Outer Apply To return null values

I have the following tables:

User which has a primary key and Username e.g.

ID    Username       
1     Fred        
2     John       
3     Jack        

Event which has p开发者_如何转开发rimary key and event name

ID    Eventname       
1     Ferrari Road Show
2     Flower Show

UserStatusUpdates table which has primary key, a status update and a userid (foreign key)

ID   UserID   UserStatus
1    1        Really Good
2    1        Leaving Now
3    2        I concur

And An Event Attendee table which has a primary key and two foreign keys (Event primary key and User primary key)

ID UserID   EventID
1  1        1
2  2        1
3  3        1

The problem I am encountering is that I need to return all the event attendees plus their latest status update, however there are cases where the user has not actually made a status update.

This is what my query looks like:

SELECT EventAttendee.*, Users.UserName,Users.USERS_ID, 
Users.ThumbnailPic,
Users.CountryName,
ISNULL(UserStatusUpdates.UserStatus,'No Updates')AS LastUpdate,
UserStatusUpdates.MediaTypeID,UserStatusUpdates.USERSTATUS_ID,(UserStatusUpdates.AddDate)

FROM EventAttendee
JOIN Users ON Events.UserID = Users.USERS_ID
OUTER APPLY  (SELECT TOP 1 UserStatusUpdates.UserStatus,UserStatusUpdates.MediaTypeID,
UserStatusUpdates.USERSTATUS_ID,UserStatusUpdates.AddDate, UserStatusUpdates.UserID
  FROM UserStatusUpdates  where UserStatusUpdates.UserID = Users.USERS_ID
ORDER BY AddDate DESC) AS UserStatusUpdates WHERE UserStatusUpdates.UserID = EventAttendee.UserID

WHERE EventAttendee.EventID = @EventID 
AND Users.bDeleted = 'False' 
AND Users.bSuspended =  'False'
END 

How can I get back users who may not have made an update?

---

Your query has multiple where clauses, and you're already specifying the UserStatusUpdates.UserID = Users.USERS_ID join in the subquery. Try this:

SELECT 
  EventAttendee.*, 
  Users.UserName,Users.USERS_ID, 
  Users.ThumbnailPic,
  Users.CountryName,
  ISNULL(UserStatusUpdates.UserStatus,'No Updates') AS LastUpdate,
  UserStatusUpdates.MediaTypeID,
  UserStatusUpdates.USERSTATUS_ID,
  UserStatusUpdates.AddDate
FROM 
  EventAttendee
  JOIN Users ON 
    Events.UserID = Users.USERS_ID
  OUTER APPLY (
    SELECT TOP 1 
      UserStatusUpdates.UserStatus,
      UserStatusUpdates.MediaTypeID,
      UserStatusUpdates.USERSTATUS_ID,
      UserStatusUpdates.AddDate,
      UserStatusUpdates.UserID
    FROM 
      UserStatusUpdates  
    WHERE 
      UserStatusUpdates.UserID = Users.USERS_ID
    ORDER BY 
      AddDate DESC
  ) AS UserStatusUpdates 
WHERE 
  EventAttendee.EventID = @EventID 
  AND Users.bDeleted = 'False' 
  AND Users.bSuspended =  'False'
END