显示权限表中的所有数据 [英] show all Data from Permission Table

问题描述

我有2个这样的表：

I have 2 tables like that:

PermissionsTbl

PermissionID int NotNull 
PermissionDescription nvarchar(100) NotNull

PermissionID PermissionDescription 
1 Human Resources 
2 Employees Data 
3 Departements

ActivePermissionsTbl

ActivePermID bigint NotNull 
PermissionID int    NotNull 
UserID       int    NotNull 
PageActive   bit    NotNull

ActivePermID PermissionID UserID   PageActive
1            1            1        True 
2            2            1        True 
3            3            2        True

what I want is show data like that:

PermissionID      PermissionDescription     PageActive     UserID
1                 Human Resources           True           1 
2                 Employees Data            True           1 
3                 Departements                             1 
1                 Human Resources                          2 
2                 Employees Data                           2 
3                 Departements              True           2

我尝试了几种Join方法，但是我失败了<任何建议请。谢谢。

I try several methods of Join , but I failed< any suggestion please. Thanks.

推荐答案

这样做：

This will do:

select p.PermissionID, p.PermissionDescription, a.PageActive, a.UserID
FROM permissionTbl p INNER JOIN activePermissionTbl a ON a.PermissionID = p.PermissionID
UNION ALL
(select b.PermissionID, b.PermissionDescription, b.PageActive, b.UserID
FROM
(select p.PermissionID, p.PermissionDescription, '' AS PageActive, a.UserID
FROM permissionTbl p INNER JOIN (SELECT DISTINCT UserID FROM activePermissionTbl) AS a ON 1 = 1) b
LEFT JOIN activePermissionTbl a ON b.PermissionID = a.PermissionID and b.UserID = a.UserID
WHERE a.UserID IS NULL)
ORDER BY 4, 1

< a href =http://www.sqlfiddle.com/#!3/1c4ad/12> SQL小提琴 [ ^ ]

试试这个...

try this...

WITH TEMPCTE AS (
SELECT permissionTbl.PermissionDescription,permissionTbl.PermissionID,A.UserID FROM permissionTbl
CROSS JOIN (SELECT DISTINCT activePermissionTbl.UserID FROM activePermissionTbl) AS A 
)
SELECT TEMPCTE.PermissionID,TEMPCTE.PermissionDescription,ISNULL(PageActive,'') PageActive,
TEMPCTE.UserID FROM TEMPCTE LEFT JOIN activePermissionTbl A
ON TEMPCTE.PermissionID = A.PermissionID AND TEMPCTE.UserID = A.UserId

快乐编码......

Happy coding...

这篇关于显示权限表中的所有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！