选择不在特定团队中的所有用户

I want to select all users from the database that is not within the particular team ID I'm looking at. Trying to make an 'add a member' function which is supposed to list all members that are not in the active team.

I just can't seem to get the SQL right.. Either I get nothing, or I still get some members that are within the active team and others, which I don't want.

This is the database, IMGUR

So in this case, viewing team ID 77 on the website, I wanna see only "Admin", as he is the one member that is not within that particular team.

Any help would be much appreciated.

I think this is what you need:

SELECT *
FROM User
WHERE userid NOT IN (
    SELECT userid
    FROM UserTeam
    WHERE teamid = ...
)

It returns all users except those in the specific team.

I'm adding this in as an answer to explain what you need to do:

You need to get your joins right. The problem is that you don't have a foreign key to join on your team id. You should be able to join your team table with the users (or anything else). But you need the team id key in there to do that.

Your tables are good in that you have unique information and IDs for your team names and you users. But you don't have any way to join them.

Add a column to the users table that is the same name and schema as the team id in the team table. Us THAT for your join.

The in (or not in) clause has a limit, consider joining instead.

Using your example, query for all users that aren't on team 77

select u.* 
from Users as u
join UserTeam as ut on ut.userid = u.userid
where ut.teamid <> 77

The above will not work for users that are on more than one team. If users can be on more than one team then

select u.* 
from Users as u
where not exists (
    select ut.userid 
    from UserTeam as ut 
    where 
            ut.teamid = 77 
        and ut.userid = u.userid
)

@RonDeijker's answer is correct. Alternative ways are

SELECT ...
FROM Users u
WHERE NOT EXISTS (
  SELECT 1
  FROM UserTeam ut
  WHERE ut.userid = u.userid
  AND ut.teamid = <whatever>
)

or

SELECT ...
FROM Users u
LEFT OUTER JOIN UserTeam ut
  ON u.userid = ut.userid AND ut.teamid = <whatever>
WHERE ut.userid IS NULL