TSQL - Select from table with multiple join paths

Question

That title is not very good, so consider the following. I have five tables:

User {
    Id,
    ProfileId        // -> Profiles.Id
}

Profile {
    Id
}

ProfilePermissionSets {
    ProfileId        // -> Profiles.Id
    PermissionSetId  // -> PermissionSets.Id
}

UserPermissionSets {
    UserId           // -> Users.Id
    PermissionSetId  // -> PermissionSets.Id
}

PermissionSets {
    Id
}

Permissions {
    Id,
    PermissionSetId  // -> PermissionSets.Id
}

And I want get all of the permissions for a user that are directly linked to it or indirectly through the profile. The not-quite-there SQL I've come up with so far is this:

SELECT  [Pe].[Controller],
        [Pe].[Action]
FROM    [PermissionSets] AS [PS]
        JOIN [UserPermissionSets] AS [UPS]
            ON ([UPS].[PermissionSetId] = [PS].[Id])
        JOIN [Users] AS [U]
            ON ([U].[Id] = [UPS].[UserId])
        JOIN [Profiles] AS [P]
            ON ([P].[Id] = [U].[ProfileId])
        JOIN [ProfilePermissionSets] AS [PPS]
            ON ([PPS].[ProfileId] = [P].[Id])
        JOIN [Permissions] AS [Pe]
            ON ([Pe].[PermissionSetId] = [PS].[Id])
WHERE   [U].[Id] = 4;

It returns back a correct count of rows, but it's repeating the controller or action over and over, so it's wrong. I'm hoping someone can help me correct it to show all of the distinct permission sets for the user. Ideally, I'd like to also change it so that it's all discovered starting at the user because that is what I have access to in the method I need to do this (the object is an Entity Framework class named User and will be browsed using LINQ).

UPDATED because I forgot that I really wanted the permissions not the permission sets.

Answer 1

Try this SQL

SELECT  [Pe].[Controller],
        [Pe].[Action]
FROM    [Users] AS [U]
    LEFT OUTER JOIN [UserPermissionSets] AS [UPS]
            ON ([UPS].[UserId] = [U].[Id])
    LEFT OUTER JOIN [ProfilePermissionSets] AS [PPS]
            ON ([PPS].[ProfileId] = [U].[ProfileId])
    LEFT OUTER JOIN [Permissions] AS [Pe]
            ON ([Pe].[PermissionSetId] = [UPS].[PermissionSetId])
            OR ([Pe].[PermissionSetId] = [PPS].[PermissionSetId])
WHERE   [U].[Id] = 4;