SQLTeam.com | Weblogs | Forums

User Notification Query

sql2012

#1

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

#2

But what trouble are you having? What does your query produce? What should it produce (please give examples)


#3

Thank you for your response.

The trouble that I am having is the results from the query will not display the Announcements from the CWAnnouncements table that was not inserted in the NotificationArchive table. The CWAnnouncements table should include original entries of the Annoncement and the NotificationArchive table is just there to indicate that the user read it or not. Also, it is having trouble deciphering which users has not inserted the individual Announcement into the NotificationArchive table.

I'll post a DDL

Thanks,

Create Table Users (
UserID varchar(50) Primary Key,
L504 int(1),
EXP int(1),
PSA int(1),
SRV int(1),
LEG int(1),
IT int(1),
MGR int(1),
AdminSupport int(1),
Fileroom int(1),
Collateral int(1),
LS bit,
LSA bit
)


Create Table CWAnnouncements (
AnnouncementID numerical Primary Key,
Title varchar(20),
UserID varchar(50),
AllStaff bit,
L504 int(1),
EXP int(1),
PSA int(1),
SRV int(1),
LEG int(1),
IT int(1),
MGR int(1),
AdminSupport int(1),
Fileroom int(1),
Collateral int(1),
LSA bit,
LS bit,
Active bit
)


Create Table NotificationArchive (
NotificationID numerical Primary Key,
AnnouncementID (numeric 18),
UserID varchar(50),
Active bit
)


insert into Users (UserID, L504, EXP, PSA, SRV, LEG, IT, MGR, AdminSupport, Fileroom, Collateral, LS, LSA) 
values ('DWalker','0','0','0','0','0','1','0','0','0','0','0','0'),
  ('SCarr','1','0','0','0','0','0','0','0','0','0','1','0'),
  ('PMarzett','0','0','0','0','0','0','1','0','0','0','0','0'),
  ('OSmith','0','0','0','0','0','0','0','0','1','0','0','0'),
  ('LLove','0','0','0','0','0','1','0','0','0','0','0','0')
  
  
insert into CWAnnouncements (AnnouncementID, Title, AllStaff, L504, EXP, PSA, SRV, LEGAL, IT, MGR, AdminSupport, Fileroom, Collateral, LSA, LS, Active) 
values ('1','Meeting Today','1','0','0','0','0','0','0','0','0','0','0','0','0','1'),
  ('2','Server','0','0','0','0','0','0','1','0','0','0','0','0','0','1'),
  ('3','Firedrill','0','0','0','0','0','0','0','1','0','0','0','0','0','1'),
  ('4','Admin','1','0','0','0','0','0','1','0','0','0','0','0','0','1'),
  ('5','Test','1','0','0','0','0','0','0','1','0','0','0','0','1','1')
  
insert into NotificationArchive (NotificationID, AnnouncementID, UserID, Active) 
('1','1','DWalker','1'),
('2','5','PMarzett','1'),
('3','4','DWalker','0'),
('4','4','OSmith','1'),

Results Desired For [DWalker]

CWAnnouncements.AnnouncementID | CWAnnouncements.Title | CWAnnouncements.AllStaff | CWAnnouncements.L504 | CWAnnouncements.EXP | CWAnnouncements.PSA | CWAnnouncements.SRV | CWAnnouncements.LEGAL | CWAnnouncements.IT | CWAnnouncements.MGR | CWAnnouncements.AdminSupport | CWAnnouncements.Fileroom | CWAnnouncements.Collateral | CWAnnouncements.LSA | CWAnnouncements.LS | NotificationArchive.NotificationID | NotificationArchive.UserID | NotificationArchive.Active
2 | Server | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
4 | Admin | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | DWalker | 0 |
5 | Test | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |


Results Desired For [PMarzett]

CWAnnouncements.AnnouncementID | CWAnnouncements.Title | CWAnnouncements.AllStaff | CWAnnouncements.L504 | CWAnnouncements.EXP | CWAnnouncements.PSA | CWAnnouncements.SRV | CWAnnouncements.LEGAL | CWAnnouncements.IT | CWAnnouncements.MGR | CWAnnouncements.AdminSupport | CWAnnouncements.Fileroom | CWAnnouncements.Collateral | CWAnnouncements.LSA | CWAnnouncements.LS | NotificationArchive.NotificationID | NotificationArchive.UserID | NotificationArchive.Active
3 | Firedrill | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
4 | Admin | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |

#4

Your DDL is not valid SQL, e.g. in Create Table Users you have:

Create Table Users (
UserID varchar(50) Primary Key,
L504 (numeric 1),

but "(numeric 1)" is not valid in SQL Server.

Are you running a different database? This forum is for SQL Server users.


#5

My fault I made an update to my previous post.


#6

is also not valid. Are you sure you are running SQL Server?


#7

Oh is it because I added (1)? Yes I am sure I am using SQL Server with dreamweaver's binding.

Create Table Users (
UserID varchar(50) Primary Key,
L504 int,
EXP int,
PSA int,
SRV int,
LEG int,
IT int,
MGR int,
AdminSupport int,
Fileroom int,
Collateral int,
LS bit,
LSA bit
)


Create Table CWAnnouncements (
AnnouncementID numerical Primary Key,
Title varchar(20),
UserID varchar(50),
AllStaff bit,
L504 int,
EXP int,
PSA int,
SRV int,
LEG int,
IT int,
MGR int,
AdminSupport int,
Fileroom int,
Collateral int,
LSA bit,
LS bit,
Active bit
)


Create Table NotificationArchive (
NotificationID numerical Primary Key,
AnnouncementID (numeric 18),
UserID varchar(50),
Active bit
)


insert into Users (UserID, L504, EXP, PSA, SRV, LEG, IT, MGR, AdminSupport, Fileroom, Collateral, LS, LSA) 
values ('DWalker','0','0','0','0','0','1','0','0','0','0','0','0'),
  ('SCarr','1','0','0','0','0','0','0','0','0','0','1','0'),
  ('PMarzett','0','0','0','0','0','0','1','0','0','0','0','0'),
  ('OSmith','0','0','0','0','0','0','0','0','1','0','0','0'),
  ('LLove','0','0','0','0','0','1','0','0','0','0','0','0')
  
  
insert into CWAnnouncements (AnnouncementID, Title, AllStaff, L504, EXP, PSA, SRV, LEGAL, IT, MGR, AdminSupport, Fileroom, Collateral, LSA, LS, Active) 
values ('1','Meeting Today','1','0','0','0','0','0','0','0','0','0','0','0','0','1'),
  ('2','Server','0','0','0','0','0','0','1','0','0','0','0','0','0','1'),
  ('3','Firedrill','0','0','0','0','0','0','0','1','0','0','0','0','0','1'),
  ('4','Admin','1','0','0','0','0','0','1','0','0','0','0','0','0','1'),
  ('5','Test','1','0','0','0','0','0','0','1','0','0','0','0','1','1')
  
insert into NotificationArchive (NotificationID, AnnouncementID, UserID, Active) 
('1','1','DWalker','1'),
('2','5','PMarzett','1'),
('3','4','DWalker','0'),
('4','4','OSmith','1'),

Results Desired For [DWalker]

CWAnnouncements.AnnouncementID | CWAnnouncements.Title | CWAnnouncements.AllStaff | CWAnnouncements.L504 | CWAnnouncements.EXP | CWAnnouncements.PSA | CWAnnouncements.SRV | CWAnnouncements.LEGAL | CWAnnouncements.IT | CWAnnouncements.MGR | CWAnnouncements.AdminSupport | CWAnnouncements.Fileroom | CWAnnouncements.Collateral | CWAnnouncements.LSA | CWAnnouncements.LS | NotificationArchive.NotificationID | NotificationArchive.UserID | NotificationArchive.Active
2 | Server | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
4 | Admin | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | DWalker | 0 |
5 | Test | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |


Results Desired For [PMarzett]

CWAnnouncements.AnnouncementID | CWAnnouncements.Title | CWAnnouncements.AllStaff | CWAnnouncements.L504 | CWAnnouncements.EXP | CWAnnouncements.PSA | CWAnnouncements.SRV | CWAnnouncements.LEGAL | CWAnnouncements.IT | CWAnnouncements.MGR | CWAnnouncements.AdminSupport | CWAnnouncements.Fileroom | CWAnnouncements.Collateral | CWAnnouncements.LSA | CWAnnouncements.LS | NotificationArchive.NotificationID | NotificationArchive.UserID | NotificationArchive.Active
3 | Firedrill | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
4 | Admin | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |

#8

I reformatted your SQL:

SELECT ROW_NUMBER() OVER (
  ORDER BY StartDate DESC
  ) AS 'Rownumber', u.USERID, CWNotifications.*
FROM users u
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.leg, 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 (
   u.MGR >= CWNotifications.MGR
   AND CWNotifications.MGR >= 1
   OR u.QC >= CWNotifications.QC
   AND CWNotifications.QC >= 1
   OR u.IT >= CWNotifications.IT
   AND CWNotifications.IT >= 1
   OR u.LEG >= CWNotifications.Legal
   AND CWNotifications.Legal > 1
   OR (
    u.AdminSupport = CWNotifications.AdminSupport
    AND CWNotifications.AdminSupport = 1
    OR u.Fileroom = CWNotifications.Fileroom
    AND CWNotifications.Fileroom = 1
    OR u.Collateral = CWNotifications.Collateral
    AND CWNotifications.Collateral = 1
    )
   OR (
    u.L504 >= CWNotifications.L504
    AND CWNotifications.L504 >= 1
    OR u.[EXP] >= CWNotifications.EXP
    AND CWNotifications.EXP >= 1
    OR u.SRV >= CWNotifications.SRV
    AND CWNotifications.SRV >= 1
    OR u.PSA >= CWNotifications.PSA
    AND CWNotifications.PSA >= 1
    )
   AND (
    u.LS = CWNotifications.LS
    AND CWNotifications.LS = 1
    OR u.LSA = CWNotifications.LSA
    AND CWNotifications.LSA = 1
    )
   OR CWNotifications.AllStaff = 1
   )
  AND CWNotifications.Active = 1
WHERE (u.USERID = '#GetAuthUser()#')
ORDER BY StartDate DESC

However, there are some missing columns:

Msg 207, Level 16, State 1, Line 69
Invalid column name 'Message'.
Msg 207, Level 16, State 1, Line 69
Invalid column name 'StartDate'.
Msg 207, Level 16, State 1, Line 69
Invalid column name 'EndDate'.
Msg 207, Level 16, State 1, Line 69
Invalid column name 'QC'.
Msg 207, Level 16, State 1, Line 86
Invalid column name 'QC'.