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