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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Top 1 with a left join

Examples related to sql-server

Examples related to outer-join