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

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.

Answer 2

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.