Top 1 avec une jointure gauche

Question

Avec la requête ci-dessous, il peut y avoir plusieurs lignes dans dps_markers avec la même clé de marqueur mais nous voulons seulement joindre la première. Si je prends cette requête et que j'enlève les 1 premiers et ORDER BY, j'obtiens une valeur pour mbg.marker_value mais si elle est exécutée telle quelle, elle renvoie toujours 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

Utilisez OUTER APPLY au lieu de 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';

Contrairement à JOIN, APPLY vous permet de référencer le u.id dans la requête interne.

Answer 2

La clé du débogage de ce genre de situation est d'exécuter la sous-requête/vue en ligne seule pour voir le résultat :

  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

En exécutant cela, vous verriez que le profile_id ne correspondait pas à la valeur u.id valeur de u162231993 ce qui expliquerait pourquoi tout mbg les références renverraient null (grâce à la jointure gauche ; vous n'obtiendriez rien si c'était une jointure interne).

Vous vous êtes mis dans le pétrin en utilisant TOP En effet, vous devez maintenant modifier la requête si vous souhaitez l'exécuter pour d'autres utilisateurs. Une meilleure approche serait :

   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'

Avec cela, vous pouvez changer le id dans le where pour vérifier les enregistrements de n'importe quel utilisateur du système.

Answer 3

Parce que le TOP 1 de la sous-requête ordonnée n'a pas de profile_id = 'u162231993' Retirer where u.id = 'u162231993' et voir les résultats à ce moment-là.

Exécutez la sous-requête séparément pour comprendre ce qui se passe.

Answer 4

Damir a raison,

Votre sous-requête doit s'assurer que dps_user.id est égal à um.profile_id, sinon elle prendra la première ligne qui pourrait, mais ne sera probablement pas égale à votre id 'u162231993'.

Votre requête devrait ressembler à ceci :

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'