July 14, 2015 at 6:33 pm
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
July 14, 2015 at 11:35 pm
Quick question, can you post the DDL (create table scripts), some sample data in the form of an insert statement and the expected results?
😎
July 15, 2015 at 10:39 am
Thank you,
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 |
July 15, 2015 at 11:15 am
I'm not sure about what you're doing and I can't test it right now because I don't have SSMS installed in here. However, I'm wondering if you only use 1's and 0's? Maybe you could simplify things by using bitwise operators.
Have you checked that your logical operators work as desired? It seems that you're combining ANDs and ORs without defining precedence, which might lead to unexpected results.
July 15, 2015 at 11:50 am
No I am not using bits because the user access level per department can rise from 1-3 actually. And also the user can be members of multiple departments. The filter works great until I join the NotificationArchive table and try to filter the query results based on the user authenticated and whether or not they've inserted an announcement entry in the NotificationArchive table.
July 15, 2015 at 12:50 pm
demeritrious (7/15/2015)
No I am not using bits because the user access level per department can rise from 1-3 actually. And also the user can be members of multiple departments. The filter works great until I join the NotificationArchive table and try to filter the query results based on the user authenticated and whether or not they've inserted an announcement entry in the NotificationArchive table.
You've got to get past this part of your query first, in the WHERE clause:
(dbo.NotificationArchive.UserID='#GetAuthUser()#'
AND dbo.CWAnnouncements.Active=1 AND dbo.NotificationArchive.UserID IS NULL)
Can you see that with this in your WHERE clause, there are NO records that can satisfy that part, and thus, that much of your WHERE clause serves no useful purpose. Clearly, the dbo.NotificationArchive.UserID field cannot have both the specified value and be NULL at the same time.
Also, your query has some fields SELECTed that don't appear anywhere in your declared table definitions:
Message (which might be Title), StartDate, and EndDate
Finally, you reference a QC field for the Users table and the CWNotifications query in a WHERE clause, but there is no such field in either location.
There were also some differing references to a Legal field, which is declared in the table as LEG. Thus, there are far too many syntax problems for me to do much with this. Additionally, your JOIN logic with the large number of conditions is particularly arduous, and needs to be more clearly defined. It may be a matter of missing a fair number of right parentheses, but then I'm just guessing and I wouldn't bet one red cent on that guess. Please elaborate and be as specific as possible.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply