T-SQL Right Joins to ALL Entries inc Selected Column

Question

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

Answer 1

SELECT  u.USERID,
        u.ADusername,
        al.ACCESSLEVELID,
        al.AccessLevelName,
        CASE WHEN ar.accesslevelid IS NULL THEN 'False' ELSE 'True' END AS Access
FROM    tblusers u
CROSS JOIN
        tblaccesslevels al
LEFT JOIN
        tblaccessrights ar
ON      ar.ACCESSLEVELID = al.ACCESSLEVELID
        AND ar.USERID = u.USERID