使用外部应用返回空值

时间:2021-01-01 09:26:06

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 primary 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)

UserStatusUpdates表具有主键,状态更新和用户标识(外键)

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?

如何找回可能未进行更新的用户?

1 个解决方案

#1


2  

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

您的查询有多个where子句,并且您已在子查询中指定UserStatusUpdates.UserID = Users.USERS_ID连接。尝试这个:

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 

#1


2  

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

您的查询有多个where子句,并且您已在子查询中指定UserStatusUpdates.UserID = Users.USERS_ID连接。尝试这个:

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