SQL Server Get Latest Rows From Table Joined to Another Table

Question

Needing a bit of a help here with an SQL Server query.

Pretty much, I need to display all the players with their corresponding latest status. I've devised my query as below, but one player who does not have any status is not showing up. I need for that player to be displayed on the list and his StatusDesc would simply be NULL.

As you can see from the final select query I have, I am missing a player (Ray Allen) who does not have a record in the PlayerStatus table.

The setup as follows:

/*Create the Temp Tables*/

CREATE TABLE #Player(
PlayerID int,
PlayerName varchar(50), 
PlayerTeam varchar(150));

CREATE TABLE #PlayerStatus(
PlayerStatusID int IDENTITY(1, 1),
PlayerID int,
StatusID int,
CreatedDate date);

CREATE TABLE #Status(
StatusID int,
StatusDesc varchar(20));

/* Populate Tables */
INSERT INTO #Player VALUES(1, 'Tim Duncan', 'Spurs');
INSERT INTO #Player VALUES(2, 'Tony Parker', 'Spurs');
INSERT INTO #Player VALUES(3, 'Manu Ginobili', 'Spurs');
INSERT INTO #Player VALUES(4, 'Boris Diaw', 'Spurs');
INSERT INTO #Player VALUES(5, 'Kawhi Leonard', 'Spurs');

INSERT INTO #Player VALUES(6, 'Lebron James', 'Heat');
INSERT INTO #Player VALUES(7, 'Dwayne Wade', 'Heat');
INSERT INTO #Player VALUES(8, 'Chris Bosh', 'Heat');
INSERT INTO #Player VALUES(9, 'Mario Chalmers', 'Heat');
INSERT INTO #Player VALUES(10, 'Udonis Haslem', 'Heat');
INSERT INTO #Player VALUES(11, 'Ray Allen', 'Heat');


INSERT INTO #PlayerStatus(PlayerID, StatusID, CreatedDate)
SELECT PlayerID, 1, GETDATE()
FROM #Player;

INSERT INTO #PlayerStatus(PlayerID, StatusID, CreatedDate)
VALUES(8, 4, GETDATE());

DELETE FROM #PlayerStatus WHERE PlayerID = 11;

INSERT INTO #Status(StatusID, StatusDesc)
VALUES(1, 'Healthy');

INSERT INTO #Status(StatusID, StatusDesc)
VALUES(2, 'Injured');

INSERT INTO #Status(StatusID, StatusDesc)
VALUES(3, 'Retired');

INSERT INTO #Status(StatusID, StatusDesc)
VALUES(4, 'Reserved');

--SELECT * FROM #Player;
--SELECT * FROM #PlayerStatus;
--SELECT * FROM #Status;

/* Select all Players and their Status */
SELECT ply.PlayerName, ply.PlayerTeam, sta.StatusDesc, pls.CreatedDate
FROM #PlayerStatus pls
INNER JOIN #Player ply ON ply.PlayerID = pls.PlayerID
INNER JOIN #Status sta ON pls.StatusID = sta.StatusID
LEFT JOIN #PlayerStatus pls2 ON pls2.PlayerID = pls.PlayerID AND pls2.PlayerStatusID > pls.PlayerStatusID
WHERE pls2.PlayerStatusID IS NULL
ORDER BY ply.PlayerID ASC;

DROP Table #Player;
DROP Table #PlayerStatus;
DROP Table #Status;

Hopefully someone can provide insight on how I would need to change my query.

Answer 1

You're close - you just need to start with the Player table, rather than the PlayerStatus, and use LEFT JOINs.

/* Select all Players and their Status */
SELECT ply.PlayerName, ply.PlayerTeam, sta.StatusDesc, pls.CreatedDate
FROM #Player ply
LEFT JOIN #PlayerStatus pls ON ply.PlayerID = pls.PlayerID
LEFT JOIN #Status sta ON pls.StatusID = sta.StatusID
LEFT JOIN #PlayerStatus pls2 ON pls2.PlayerID = pls.PlayerID AND pls2.PlayerStatusID > pls.PlayerStatusID
WHERE pls2.PlayerStatusID IS NULL
ORDER BY ply.PlayerID ASC;

Your original query started with PlayerStatus, and linked out from there, so it started with just those values that existed in the PlayerStatus table.

You were also using INNER JOINs, which limit your results to only those records which match. Using a LEFT JOIN instead (or a RIGHT JOIN, I suppose, but I find it easier to read with a LEFT JOIN) will allow you to retrieve all records from the first table, regardless of whether they have a match in the second table. See here.