User Notification Query

[demeritrious]

Good evening,



I am working on a query at my office that is joining multiple tables in order to display a User notification (similar to facebook). The notifications are based on user department, access level, and position. I want to display all of the  announcements filtered for the user that is in the CWAnnouncement table and the NotificationArchive Table where the NotificationArchive.Active is equal to 1 or if they have not made an entry in the NotificationArchive table. If NotificationArchive.Active is equal to 0 then I do not want it to show up in the query results. 



So far the user filter works but I am having trouble display the results based on who the user is and whether or not they have an announcement in the NotificationArchive table. I hope this make sense. 



Help would be greatly appreciated this will be my first major project for this company and I want to do a good job for them.



Thanks,


 

SELECT ROW_NUMBER() OVER(ORDER BY StartDate DESC) AS 'Rownumber', dbo."User".USERID, CWNotifications.*
FROM dbo."User"
LEFT JOIN
    (
SELECT dbo.CWAnnouncements.AnnouncementID, dbo.CWAnnouncements.Title, dbo.CWAnnouncements.Message, dbo.CWAnnouncements.StartDate, dbo.CWAnnouncements.EndDate, dbo.CWAnnouncements.AllStaff, dbo.CWAnnouncements.MGR, dbo.CWAnnouncements.L504, dbo.CWAnnouncements.AdminSupport, dbo.CWAnnouncements.EXP, dbo.CWAnnouncements.IT, dbo.CWAnnouncements.Legal, dbo.CWAnnouncements.PSA, dbo.CWAnnouncements.SRV, dbo.CWAnnouncements.QC, dbo.CWAnnouncements.Active, dbo.CWAnnouncements.UserID, dbo.CWAnnouncements.LS, dbo.CWAnnouncements.LSA, dbo.CWAnnouncements.FileRoom, dbo.CWAnnouncements.Collateral, NotificationArchive.NotificationID, NotificationArchive.Active AS ActiveNote
FROM dbo.CWAnnouncements
Left JOIN
dbo.NotificationArchive
ON dbo.CWAnnouncements.AnnouncementID=dbo.NotificationArchive.AnnouncementID
WHERE (dbo.NotificationArchive.UserID='#GetAuthUser()#'
AND dbo.CWAnnouncements.Active=1 AND dbo.NotificationArchive.UserID IS NULL) OR dbo.CWAnnouncements.UserID='#GetAuthUser()#' AND dbo.NotificationArchive.UserID='#GetAuthUser()#'
AND dbo.CWAnnouncements.Active=1 AND dbo.NotificationArchive.Active = 1
)
CWNotifications
ON (dbo."User".MGR>=CWNotifications.MGR AND CWNotifications.MGR >=1
OR
dbo."User".QC>=CWNotifications.QC AND CWNotifications.QC >=1
OR
dbo."User".IT>=CWNotifications.IT AND CWNotifications.IT >=1
OR
Dbo."User".LEG>=CWNotifications.Legal AND CWNotifications.Legal >1
OR
(dbo."User".AdminSupport=CWNotifications.AdminSupport AND CWNotifications.AdminSupport = 1
OR dbo."User".Fileroom=CWNotifications.Fileroom AND CWNotifications.Fileroom = 1
OR dbo."User".Collateral=CWNotifications.Collateral AND CWNotifications.Collateral = 1)
OR
(dbo."User".L504>=CWNotifications.L504 AND CWNotifications.L504 >= 1
 or dbo."User".EXP>=CWNotifications.EXP AND CWNotifications.EXP >= 1
or dbo."User".SRV>=CWNotifications.SRV AND CWNotifications.SRV >= 1
or dbo."User".PSA>=CWNotifications.PSA AND CWNotifications.PSA >= 1)
AND
(dbo."User".LS=CWNotifications.LS AND CWNotifications.LS = 1 OR dbo."User".LSA=CWNotifications.LSA AND CWNotifications.LSA = 1)
OR CWNotifications.AllStaff=1)
AND CWNotifications.Active=1
WHERE (dbo."user".USERID='#GetAuthUser()#')
ORDER BY StartDate DESC

[Muddy_Funster]

what have you tried?

 

What do you get if you change the following LEFT join to an INNER join?

 

Also, there is a spurious closing parenthesis ) in the WHERE clause that could be causing an issue. You should wrap all complex conditions inside parenthesis to ensure that the clause is checked accurately

FROM dbo.CWAnnouncements
Left JOIN
dbo.NotificationArchive
ON dbo.CWAnnouncements.AnnouncementID=dbo.NotificationArchive.AnnouncementID
WHERE (dbo.NotificationArchive.UserID='#GetAuthUser()#'
AND dbo.CWAnnouncements.Active=1 AND dbo.NotificationArchive.UserID IS NULL) OR dbo.CWAnnouncements.UserID='#GetAuthUser()#' AND dbo.NotificationArchive.UserID='#GetAuthUser()#'
AND dbo.CWAnnouncements.Active=1 AND dbo.NotificationArchive.Active = 1
)

Edited July 22, 2015 by Muddy_Funster