TSQL-从具有多个联接路径的表中选择

Gup3rSuR4c

该标题不是很好，因此请考虑以下内容。我有五张桌子：

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
}

我想获得直接链接到该用户或通过配置文件间接链接到该用户的所有权限。到目前为止，我还没有想到的SQL是这样的：

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;

它返回正确的行数，但是要一遍又一遍地重复控制器或动作，所以这是错误的。我希望有人可以帮助我更正它，以显示该用户的所有不同权限集。理想情况下，我还要更改它，以便从用户处发现所有内容，因为这是我需要执行的方法中可以访问的（该对象是一个名为Entity Framework的类User，将使用LINQ进行浏览。）。

更新，因为我忘记了我真正想要的是权限而不是权限集。

阿卜杜勒·拉希德（Abdul Rasheed）

试试这个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;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。