T-SQL Right Joins to ALL Entries inc Selected Column
- by Pace
Hi Experts,
I have the following Query which produces the output below;
SELECT
TBLUSERS.USERID,
TBLUSERS.ADusername,
TBLACCESSLEVELS.ACCESSLEVELID,
TBLACCESSLEVELS.AccessLevelName
FROM
TBLACCESSLEVELS INNER JOIN
TBLACCESSRIGHTS ON TBLACCESSLEVELS.ACCESSLEVELID = TBLACCESSRIGHTS.ACCESSLEVELID INNER JOIN
TBLUSERS ON TBLACCESSRIGHTS.USERID = TBLUSERS.USERID
The output is this;
29 administrator 1 AllUsers
29 administrator 2 JobQueue
29 administrator 3 Telephone Directory Admin
29 administrator 4 Jobqueueadmin
29 administrator 5 UserAdmin
29 administrator 6 Product System
27 alan 1 AllUsers
97 andy 1 AllUsers
26 barry 1 AllUsers
26 barry 2 JobQueue
26 barry 3 Telephone Directory Admin
26 barry 4 Jobqueueadmin
26 barry 5 UserAdmin
26 barry 6 Product System
26 barry 7 Newseditor
26 barry 8 GreetingBoard
What I would like to do is modify the query so I get all Access Levels regardless of weather there is an entry for that user. What I would also like to do is some sort of exist case so that I get output like the following;
29 administrator 1 AllUsers True
29 administrator 2 JobQueue True
29 administrator 3 Telephone Directory Admin True
29 administrator 4 Jobqueueadmin True
29 administrator 5 UserAdmin True
29 administrator 6 Product System True
29 administrator 7 Newseditor False
29 administrator 8 GreetingBoard False
27 alan 1 AllUsers True
27 alan 2 JobQueue False
27 alan 3 Telephone Directory Admin False
27 alan 4 Jobqueueadmin False
27 alan 5 UserAdmin False
27 alan 6 Product System False
27 alan 7 Newseditor False
27 alan 8 GreetingBoard False
97 andy 1 AllUsers True
97 andy 2 JobQueue False
97 andy 3 Telephone Directory Admin False
97 andy 4 Jobqueueadmin False
97 andy 5 UserAdmin False
97 andy 6 Product System False
97 andy 7 Newseditor False
97 andy 8 GreetingBoard False
26 Barry 1 AllUsers True
26 Barry 2 JobQueue True
26 Barry 3 Telephone Directory Admin True
26 Barry 4 Jobqueueadmin True
26 Barry 5 UserAdmin True
26 Barry 6 Product System True
26 Barry 7 Newseditor True
26 Barry 8 GreetingBoard True
.........................................
So the rules are ALWAYS show ALL Entries for ACCESSLEVELS and where EXISTS in ACCESSRIGHTS produce a true / false to show this.
I hope this makes sense and hopefully you dont need the table definitions as everything I need to work with is in the original Query. I just need a way of manipulating it slightly and getting the join in the right place.
Thank you.
Pace
