SQL column are null overridden selectors
I want to create a SQL select statement that will JOIN multiple tables and yeidl results. Basically I have two tables that are independent of each other. One table contains users (who are supposed to have the most current and correct information) and I have a table with an owner called player. The placeholder table has a column labeled userID that is set to NULL. when the user is recognized as a player, the NULL value for the user ID is replaced with the unique user ID.
This leads me to want to use SQL to check if userID is NULL. i wrote the following expression which doesn't work
SELECT
teams.*, players.number, players.position, players.userId,
CASE WHEN players.userId IS NULL THEN players.id ELSE userPlayer.id END AS playerId,
CASE WHEN players.userId IS NULL THEN players.firstName ELSE userPlayer.firstName END AS firstName,
CASE WHEN players.userId IS NULL THEN players.lastName ELSE userPlayer.lastName END AS lastName,
CASE WHEN players.userId IS NULL THEN players.email ELSE userPlayer.email END AS email
FROM teams
LEFT JOIN players ON players.teamId = teams.id
LEFT JOIN users AS userPlayer ON userPlayer.id= players.userId
WHERE teams.id = (:t)
This seems to be very ineffective. Is there a better way?
thank
To begin with, it is userPlayer.id
never different from players.userId
(either both are something, or both are zero), so you can drop that expression.
Also, use coalesce()
instead case when x is null
for clarity and brevity:
SELECT
teams.*, players.number, players.position,
players.userId AS playerId,
COALESCE(userPlayer.firstName, players.firstName) AS firstName,
COALESCE(userPlayer.lastName, players.lastName) AS lastName,
COALESCE(userPlayer.email, players.email) AS email
FROM teams
LEFT JOIN players ON players.teamId = teams.id
LEFT JOIN users AS userPlayer ON userPlayer.id = players.userId
WHERE teams.id = (:t)
source to share
You can create a view where you can conveniently select commands by their ID. In this case, it can be optimized because if player.userId is null then all user values are null (due to left join). This way you can create a union if you have a lot of null records, this might be useful:
SELECT
teams.*, players.number, players.position, players.userId AS playerId,
players.firstName, players.lastName, players.email
FROM
teams LEFT JOIN players ON players.teamId = teams.id
WHERE
players.userId IS NULL OR
NOT EXISTS(SELECT * FROM users WHERE users.id = players.userId)
UNION
SELECT
teams.*, players.number, players.position, players.userId AS playerId,
COALESCE(userPlayer.firstName, players.firstName) AS firstName,
COALESCE(userPlayer.lastName, players.lastName) AS lastName,
COALESCE(userPlayer.email, players.email) AS email
FROM
teams
INNER JOIN players ON players.teamId = teams.id
INNER JOIN users AS userPlayer ON userPlayer.id = players.userId
source to share
If your "player.userId" is PC, you can try the following:
SELECT
teams.*, players.number, players.position, players.userId,
ISNULL(players.id, userPlayer.id) AS playerId,
ISNULL(players.firstName, userPlayer.firstName) AS firstName,
ISNULL(players.lastName, userPlayer.lastName) AS lastName,
ISNULL(players.email, userPlayer.email) AS email
FROM teams
LEFT JOIN players ON players.teamId = teams.id
LEFT JOIN users AS userPlayer ON userPlayer.id= players.userId
WHERE teams.id = (:t)
source to share