代码之家 › 专栏 › 技术社区 › Mick Walker

SQL Server-查询帮助

sql-server sql

Mick Walker · 技术社区 · 14 年前

我有以下三张桌子

Teams (TeamID int, TeamName varchar(200)) - TeamID is identity col
Users (UserID int, Username varchar(200)) - UserID is identity col
UsersTeams (ID int, UserID int, TeamID int) - Table has FK references to the 2 tables above

用户流表用于记录用户所属的所有团队。我想找到一种方法,在给定用户ID的情况下,列出用户不是其成员的所有团队。

当做

3 回复 | 直到 14 年前

John Hartsock 14 年前

DECLARE @UserID INT;

SET @UserID = 1;

SELECT
  *
FROM Teams
WHERE TeamID NOT IN (SELECT TeamID FROM UserTeams WHERE UserID = @UserID)

Andomar 14 年前

你可以使用 not exists :

select  *
from    teams t
where   not exists
        (
        select  *
        from    UserTeams ut
        join    User u
        on      u.UserId = ut.UserId
        where   t.TeamId = ut.TeamID
                and Users.Username = 'TheUser'
        )

anivas 14 年前

具有<gt;条件的联接可能会执行得更好。

DECLARE @UserID INT
SET @UserID = 1   

SELECT Teams.TeamID, Teams.TeamName FROM Teams
INNER JOIN UsersTeams 
ON Teams.TeamID <> UsersTeams.TeamID
WHERE UsersTeams.UserID = @UserID

推荐文章