SQL how to show players not currently in a team

I am trying to show players in a drop down view which is not already added to the chosen team.

When I put a = sign in my SQL-question I get all the players added to the team, but when I change it to <> it instead shows all players not in the current team but all other teams.

My problem is that I can't make a SQL-question that shows all players (PID, PlayerID) you (UID, TeamUID, PlayerUID) have created that is not currently in the choosen team (TID, TeamID).

SELECT DISTINCT Player_tbl.* 
FROM PlayerTeam_tbl 
left JOIN Player_tbl ON Player_tbl.PID = PlayerTeam_tbl.PlayerID 
WHERE TeamID <> 14 AND UID = 2

Player_tbl

Team_tbl

PlayerTeam_tbl

Tables

Not sure which database you're on, assuming it is MS SQL the query would look something like this:

SELECT * FROM PlayerTbl 
WHERE PID NOT IN 
(SELECT PlayerID from PlayerTeam where TEAMUID = @teamid)

To get the result you want: only player 15 and 18 should be shown as play 17 are in both team 14 and team 17)

If your DB Engine support NOT IN subquery, you can simply get ther result with NOT IN subquery.

Here is the query:

SELECT Player_tbl.pid, fornamn,number,coach,uid
FROM Player_tbl
where pid NOT in (
    SELECT
        PlayerID
    FROM
        PlayerTeam_tbl
    WHERE
        TeamID = 14
)

If your DB engine dones't support NOT In sub query, you can put player_tbl in the left side of the join, and use group by operator with having clause to filter any player in team 14.

Here is the query:

SELECT Player_tbl.pid, fornamn,number,coach,uid
FROM Player_tbl 
left JOIN PlayerTeam_tbl ON Player_tbl.PID = PlayerTeam_tbl.PlayerID 
WHERE UID = 2
GROUP BY Player_tbl.pid, fornamn,number,coach,uid
HAVING sum(case when TeamID= 14 then 1 else 0 end) = 0

The simple answer would be a left-join. HOWEVER, to clarify, if you rely on that, you would get everyone NOT on the team. -- OR-- Do you mean you want a list of all players who are not on ANY team. How could one person be associated with two active teams.

SELECT 
      P.* 
   FROM 
      PlayerTbl P
         LEFT JOIN PlayerTeam PT
            on P.PlayerID = PT.PlayerID
   WHERE
      PT.PID IS NULL

The point here with the WHERE clause is that the player is not on ANY team, regardless of your interest in team 1, 2, 14, 27, whatever. If they are not on a team, they can be assigned to ANY team.