Top 1 with a left join
Use OUTER APPLY instead of LEFT JOIN:
SELECT u.id, mbg.marker_value FROM dps_user uOUTER APPLY (SELECT TOP 1 m.marker_value, um.profile_id FROM dps_usr_markers um (NOLOCK) INNER JOIN dps_markers m (NOLOCK) ON m.marker_id= um.marker_id AND m.marker_key = 'moneyBackGuaranteeLength' WHERE um.profile_id=u.id ORDER BY m.creation_date ) AS MBGWHERE u.id = 'u162231993';
Unlike JOIN, APPLY allows you to reference the u.id inside the inner query.
The key to debugging situations like these is to run the subquery/inline view on its' own to see what the output is:
SELECT TOP 1 dm.marker_value, dum.profile_id FROM DPS_USR_MARKERS dum (NOLOCK) JOIN DPS_MARKERS dm (NOLOCK) ON dm.marker_id= dum.marker_id AND dm.marker_key = 'moneyBackGuaranteeLength'ORDER BY dm.creation_date
Running that, you would see that the profile_id
value didn't match the u.id
value of u162231993
, which would explain why any mbg
references would return null
(thanks to the left join; you wouldn't get anything if it were an inner join).
You've coded yourself into a corner using TOP
, because now you have to tweak the query if you want to run it for other users. A better approach would be:
SELECT u.id, x.marker_value FROM DPS_USER uLEFT JOIN (SELECT dum.profile_id, dm.marker_value, dm.creation_date FROM DPS_USR_MARKERS dum (NOLOCK) JOIN DPS_MARKERS dm (NOLOCK) ON dm.marker_id= dum.marker_id AND dm.marker_key = 'moneyBackGuaranteeLength' ) x ON x.profile_id = u.id JOIN (SELECT dum.profile_id, MAX(dm.creation_date) 'max_create_date' FROM DPS_USR_MARKERS dum (NOLOCK) JOIN DPS_MARKERS dm (NOLOCK) ON dm.marker_id= dum.marker_id AND dm.marker_key = 'moneyBackGuaranteeLength' GROUP BY dum.profile_id) y ON y.profile_id = x.profile_id AND y.max_create_date = x.creation_date WHERE u.id = 'u162231993'
With that, you can change the id
value in the where
clause to check records for any user in the system.
Because the TOP 1
from the ordered sub-query does not have profile_id = 'u162231993'
Remove where u.id = 'u162231993'
and see results then.
Run the sub-query separately to understand what's going on.