SQL Server - Query Help

Question

I have the following 3 tables

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

The table UsersTeams is used to keep a record of all teams a user is a member of. I want to find a way, given a user id, to list all teams which a user is not a member of.

Could anyone help?

Regards

Answer 1

DECLARE @UserID INT;

SET @UserID = 1;

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

Answer 2

You could use 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'
        )

Answer 3

A join with <> condition might perform better.

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