top 1 with a left join

Question

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'

Answer 1

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.

Answer 2

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'

Answer 3

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.

Answer 4

Use OUTER APPLY instead of LEFT JOIN:

SELECT u.id, mbg.marker_value 
FROM dps_user u
OUTER 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 MBG
WHERE u.id = 'u162231993';

Unlike JOIN, APPLY allows you to reference the u.id inside the inner query.