显示权限表中的所有数据
问题描述：
我有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
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...