CODEWITHSUNDEEP
mysqljoinnull
mat buckland
mat buckland

Reputation: 21

mysql JOIN when one table has no entry

I have two tables:

player with player_id and name

team with player_id and team_id

Not all players have an entry in team.

I'm trying to create a query that, given a player_id, will return the name and null if the player_id is not found in the team table.

I've tried the various JOINs but I either get null, null or the name with the first team_id found in the table.

Thanks for any help

Upvotes: 1

Views: 1447

Answers (1)

codingbiz
codingbiz

Reputation: 26386

Do this

SELECT p.PlayerID, t.PlayerID
FROM Player p
LEFT JOIN Team t
ON p.PlayerID = t.PlayerID

If you want null to be returned pick the t.PlayerID as p.PlayerID will not be null if it exists in Player

Your result will look like this

PlayerID   Player Name    TeamID    PlayerID
-------------------------------------------------
0001       John           NULL      NULL

As you can see it is the second PlayerID that is NULL not the first. Pick the second e.g. WHERE t.PlayerID IS NULL

Upvotes: 3

Related Questions