Jump to content
Sign in to follow this  
demeritrious

User Notification Query

Recommended Posts

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

Share this post


Link to post
Share on other sites

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 by Muddy_Funster

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.