Given the query below there might be multiple rows in dps_markers with the same marker key but we only want to join against the first. If I take this query and remove the top 1 and ORDER BY I get a value for mbg.marker_value but run as it is it always returns null
SELECT u.id, mbg.marker_value
FROM dps_user u
LEFT JOIN
(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'
ORDER BY m.creation_date
) MBG ON MBG.profile_id=u.id
WHERE u.id = 'u162231993'
This question is related to
sql-server
outer-join
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.
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 u
LEFT 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.
Damir is correct,
Your subquery needs to ensure that dps_user.id equals um.profile_id, otherwise it will grab the top row which might, but probably not equal your id of 'u162231993'
Your query should look like this:
SELECT u.id, mbg.marker_value
FROM dps_user u
LEFT JOIN
(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 u.id = um.profile_id
ORDER BY m.creation_date
) MBG ON MBG.profile_id=u.id
WHERE u.id = 'u162231993'
Source: Stackoverflow.com