Get SQL to get all records from a usertable and those matching uids from second table or return null
typescript
Ethan JacksonI have two tables, usertable and namedown.
usertable contains all users uids in the system, namedown contains list of users (and their uids) and if they are able to play in specific match (nd_abletoplay and nd_matchUID = 869)
I'm looking to return all users from usertable along with the nd_abletoplay status of all the players from the 869 match or a null for the nd_abletoplay if player not in the 869 match
I hope that makes some sort of sense.
I tried
SELECT
ut.usr_uid, usr_lastname, usr_firstname,
nd.nd_AbleToPlay
FROM
usertable AS ut
LEFT JOIN
namedown AS nd ON ut.usr_uid = nd.nd_playeruid
WHERE
nd.nd_matchuid = 869 OR nd.nd_matchuid IS NULL
but not all records from the usertable were not returned which I cannot fathom.
Expecting
| usr_UID | Name | Nd_Abletoplay |
|---|---|---|
| 123 | fred | 1 |
| 124 | john | 2 |
| 125 | jane | null |
| 126 | dick | null |
| 127 | abby | null |
fred and john playing in match 689 others are not
Any help greatly appreciated
Answer
Try to test this code with data:
SELECT ut.usr_uid, usr_lastname, usr_firstname, nd.nd_AbleToPlay
FROM usertable AS ut
INNER JOIN namedown AS nd ON ut.usr_uid = nd.nd_playeruid
WHERE nd.nd_matchuid = 869
UNION ALL
SELECT ut.usr_uid, usr_lastname, usr_firstname, NULL AS nd_AbleToPlay
FROM usertable AS ut
WHERE NOT EXISTS (
SELECT 1
FROM namedown AS nd
WHERE ut.usr_uid = nd.nd_playeruid
AND nd.nd_matchuid = 869
)