I could greatly use your help with this query.
CASE WHEN b.TransferToLocationFKey is null then b.TransferToPerson else d.locationname end as
FROM EvidenceChainOfCustody b
JOIN location d on b.TransferToLocationFKey = d.uniquekey
WHERE b.EvidenceFKey = '23420sdfjl'
As you can see, the value can be pulled from 2 different tables. There are three records which should return. But it isn't returning the record where the b.TransferToLocationFKey is null.
Truly appreciate your help