Get SQL to get all records from a usertable and those matching uids from second table or return null

I 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

Read more

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

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
)