SQL Server从表中选择主键在另一个表中不显示为外键的表

I have 3 tables -

我有3张桌子 -

User                UserID PK

SecurityGroup       SecurityGroupID PK, SecurityGroupName

UserSecurityGroup   UserSecurityGroupID, UserID FK, SecurityGroupID FK

I am trying to select the names of the security groups that a user is NOT a part of.

我试图选择用户不属于的安全组的名称。

A user can be a member of more than one group.

用户可以是多个组的成员。

i.e. I have three security groups: Admin, Moderator, Member

即我有三个安全组：管理员，主持人，会员

I pass through a UserID. Said user is assigned to the Admin and Moderator groups but is NOT part of the Member group. I'm trying to display "Member".

我通过了一个UserID。所述用户被分配给Admin和Moderator组，但不属于Member组。我正在尝试显示“会员”。

These are my attempts so far:

这些是我到目前为止的尝试：

Attempt 1 -

尝试1 -

select tblSecurityGroup.SecurityGroupName
from tblSecurityGroup
    inner join tblUserSecurityGroup
        on tblSecurityGroup.SecurityGroupID = tblUserSecurityGroup.SecurityGroupID
    inner join tblUser
        on tblUserSecurityGroup.UserID = tblUser.UserID
where tblUser.UserID = 1
    and tblSecurityGroup.SecurityGroupID not in (tblUserSecurityGroup.SecurityGroupID);

Attempt 2 -

尝试2 -

select tblSecurityGroup.SecurityGroupName
from tblSecurityGroup
    inner join tblUserSecurityGroup
        on tblSecurityGroup.SecurityGroupID = tblUserSecurityGroup.SecurityGroupID
    inner join tblUser
        on tblUserSecurityGroup.UserID = tblUser.UserID
where tblUser.UserID = 1
    and not exists (select tblSecurityGroup.SecurityGroupID
                    from tblSecurityGroup
                    where tblUserSecurityGroup.SecurityGroupID = tblSecurityGroup.SecurityGroupID);

Guidance for a nooby student would be most appreciated.

对于一个不称职的学生的指导将非常感激。

1 个解决方案

#1

Your question can be answered by a not exists query. Here is one method:

您的问题可以通过不存在的查询来回答。这是一种方法：

select sg.SecurityGroupName
from tblSecurityGroup sg
where not exists (select 1
                  from tblUserSecurityGroup usg 
                  where sg.SecurityGroupID = usg.SecurityGroupID and
                        usg.UserID = 1
                 );

Note that tblUser is not needed because UserID is in tblUserSecurityGroup.

请注意，不需要tblUser，因为UserID位于tblUserSecurityGroup中。

#1