在我的应用程序中,成员拥有管理每个任务的权限。授予成员权限的方式是,每个成员都被分配到单独的组,并且每个组都有单独的权限。因此,每个组的成员都会获得授予该组的权限。
我有以下表格结构,
集团
+---------+------+
| GroupID | Name |
+---------+------+
| G1 | YYY |
+---------+------+
| G2 | XXX |
+---------+------+
| G3 | UUU |
+---------+------+
授权
+-----------------+-------------------+
| AuthorizationID | AuthorizationName |
+-----------------+-------------------+
| AUTH1 | Delete |
+-----------------+-------------------+
| AUTH2 | Send |
+-----------------+-------------------+
| AUTH3 | ADD |
+-----------------+-------------------+
Memeber
+----------+------+
| MemberID | Name |
+----------+------+
| 1 | PAUL |
+----------+------+
| 2 | JHON |
+----------+------+
| 3 | AMBE |
+----------+------+
| 4 | NISH |
+----------+------+
组授权
+----------------------+-----------+-------------------+
| GroupAuthorizationID | FKGroupID | FkAuthorizationID |
+----------------------+-----------+-------------------+
| 1 | G1 | AUTH1 |
+----------------------+-----------+-------------------+
| 2 | G2 | AUTH1 |
+----------------------+-----------+-------------------+
| 3 | G1 | AUTH2 |
+----------------------+-----------+-------------------+
| 4 | G3 | AUTH1 |
+----------------------+-----------+-------------------+
成员组
+---------------+-----------+------------+
| MemberGroupID | FKGroupID | FKMemberID |
+---------------+-----------+------------+
| 1 | G1 | 1 |
+---------------+-----------+------------+
| 2 | G2 | 1 |
+---------------+-----------+------------+
| 3 | G4 | 2 |
+---------------+-----------+------------+
| 4 | G4 | 3 |
+---------------+-----------+------------+
我需要获得所有拥有发送授权的用户。我如何向它写入联接查询。
Select M.name, A.Authorization
FROM Authorization A INNER JOIN GroupAuthorization GA
ON A.AuthorizationID = GA.FkAuthorizationID
INNER JOIN MemberGroup MG
ON MG.FKGroupID = GA.FKGroupID
INNER JOIN Memeber M ON M.MemberID = MG.FKMemberID
where A.AuthorizationName = 'Send'