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

PID	Fornamn	Number	Coach	UID
15	Stephen	85		2
16	Axel	47	nej	2
17	Kaspar	32	nej	2
18	Mats	66	nej	2
19	Oskar	99	nej	2

Team_tbl

TID	Division	Team	Strength	TemUID
14	u20	KFUM...	bankvarmare	2
17	u17	Malmo	mangd	2
20	u14	KFUM...	give and go	2
21	u17	Frovi FC	Maximus	2
23	u14	Rovani...	100	2
24	u20	Matss...	programering	3
25	u14	test	testar	2
26	u17	kalle k...	iq	2
27	u20	London	vet inte	5

PlayerTeam_tbl

PTID	PlayerID	TeamID	TeamUID
5	15	15	2
11	15	17	2
16	15	23	2
60	18	17	2
63	17	17	2
75	19	14	2
77	16	14	2
80	17	14	2

Tables

Solution

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
)

pid	fornamn	number	coach	uid
15	Stephen	85	null	2
18	Mats	66	nej	2

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

pid	fornamn	number	coach	uid
15	Stephen	85	null	2
18	Mats	66	nej	2