Top 1 with left connection

Question

Given the request below, there may be several lines in dps_markers with the same token key, but we only want to join the first. If I take this query and delete the top part 1 and ORDER BY, I will get the value for mbg.marker_value, but we will run as 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 u.id inside an internal request.

Answer 2

The key to debugging such situations is to run a subquery / inline view on your own to see what the result 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 

By running this, you will see that the profile_id value profile_id not match the u.id u162231993 value, which explains why any mbg links return null (thanks to the left join; you would not get anything if it were an inner join).

You encoded yourself in the corner using TOP , because now you need to configure the request if you want to run it for other users. The best approach:

  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' 

In doing so, you can change the id value in the where clause to check records for any user on the system.

Answer 3

Since TOP 1 from the ordered subquery does not have profile_id = 'u162231993' Remove where u.id = 'u162231993' and then view the results.

Run an additional query separately to understand what is happening.

Answer 4

Damir is right

Your subquery should ensure that dps_user.id is um.profile_id, otherwise it will grab the top line, which may, but probably does not equal your identifier 'u162231993'

Your request 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'