Comment puis-je limiter un LEFT JOIN pour le 1er résultat dans SQL Server?

J'ai un peu de SQL, qui est presque de faire ce que je veux faire. Je travaille avec trois tables, un des Utilisateurs, UserPhoneNumbers et UserPhoneNumberTypes. Je vais essayer d'obtenir une liste des utilisateurs avec leurs numéros de téléphone pour une exportation.

La base de données elle-même est ancien et a quelques problèmes d'intégrité. Mon problème est qu'il ne doit jamais être 1 type de chaque numéro de téléphone dans la base de données, mais ce n'est pas le cas. Lorsque j'exécute ce je obtenir plusieurs lignes de résultats pour chaque personne si elles contiennent, par exemple, deux "à la Maison" numéros.

Comment puis-je modifier le SQL pour prendre le premier numéro de téléphone indiqué et ignorer les autres numéros? Je suis dans SQL Server et je sais que sur le HAUT de l'instruction. Mais si j'ajoute "TOP 1" à GAUCHE REJOINDRE instruction select sa juste me donner la 1ère entrée dans la base de données, pas la 1ère entrée pour chaque Utilisateur.

C'est pour SQL Server 2000.

Merci,

SELECT  Users.UserID, 
  Users.FirstName, Users.LastName,
  HomePhone, WorkPhone, FaxNumber

FROM Users

LEFT JOIN
 (SELECT UserID, PhoneNumber AS HomePhone
 FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
 WHERE UserPhoneNumberTypes.PhoneNumberType='Home') AS tmpHomePhone
 ON tmpHomePhone.UserID = Users.UserID
LEFT JOIN
 (SELECT UserID, PhoneNumber AS WorkPhone
 FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
 WHERE UserPhoneNumberTypes.PhoneNumberType='Work') AS tmpWorkPhone
 ON tmpWorkPhone.UserID = Users.UserID
LEFT JOIN
 (SELECT UserID, PhoneNumber AS FaxNumber
 FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
 WHERE UserPhoneNumberTypes.PhoneNumberType='Fax') AS tmpFaxNumber
 ON tmpFaxNumber.UserID = Users.UserID

Qui dépend de la version de SQL, vous êtes en train de parler. Si c'est SQL Server 2005+, vous avez de nombreuses options, y compris RANG des requêtes.
SQL Server est la version 2000.
La solution est [ici][1], il suffit de remplacer jointure left join. [1]: stackoverflow.com/questions/2043259/...

InformationsquelleAutor Justin808 | 2010-07-30

6

Depuis SQL Server 2000 et les fonctions de classement sont à l'extérieur, vous pourriez faire de votre sous-requête Sélectionne globale:
```
SELECT UserID, MAX(PhoneNumber) AS HomePhone FROM [...] GROUP BY UserID
```
iff vous n'avez pas de soins d'Accueil d'un utilisateur numéros sont retournés...
- C'est certainement simple, et je l'ai utilisé assez souvent.
- Merci, pour moi le résultat n'est pas un problème, juste pour qu'il n'y en a qu'une.
InformationsquelleAutor Dan J

En supposant que SQL Server 2005+, utilisez la fonction ROW_NUMBER:

LEFT JOIN (SELECT UserID, 
                  PhoneNumber AS HomePhone,
                  ROW_NUMBER() OVER (PARTITION BY userid ORDER BY what?) AS rank
             FROM UserPhoneNumbers  upn
        LEFT JOIN UserPhoneNumberTypes upnt ON upnt.UserPhoneNumberTypeID = upn.UserPhoneNumberTypeID
                                           AND upnt.PhoneNumberType='Home') AS tmpHomePhone
                ON tmpHomePhone.UserID = Users.UserID
               AND tmpHomePhone.rank = 1

L'esprit de la what? espace réservé pour déterminer le premier numéro. Omettre l'ORDRE, si vous n'avez pas de soins du tout...

InformationsquelleAutor

7

Chaque fois que vous voulez sélectionner seulement une rangée du haut à partir d'une table de gauche pour chaque ligne dans le tableau de droite, vous devriez envisager d'utiliser l'opérateur apply au lieu de les joindre, et déplacer la condition de jointure à l'intérieur de la jointure gauche:
```
SELECT  u.UserID, 
  u.FirstName, u.LastName,
  hn.PhoneNumber AS HomePhone
FROM Users u
OUTER APPLY (
 SELECT TOP(1) PhoneNumber 
 FROM UserPhoneNumbers upn
 LEFT JOIN UserPhoneNumberTypes upt 
   ON upn.UserPhoneNumberTypeID=upt.UserPhoneNumberTypeID
 WHERE upt.PhoneNumberType='Home'
 AND upn.UserID = u.UserID
 ORDER BY ...) as hn
...
```
- Est-ce valable dans MS SQL 2000?
- Non, SQL 2005 et après seulement. J'ai omis de vous demandé pour SQL 2K uniquement.
InformationsquelleAutor Remus Rusanu

Select Users.UserID,  Users.FirstName, Users.LastName
    , PhoneNumbers.HomePhone
    , PhoneNumbers.WorkPhone
    , PhoneNumbers.FaxNumber
From Users
    Left Join   (
                Select UPN.UserId
                    , Min ( Case When PN.PhoneNumberType = 'Home' Then UPN.PhoneNumber End ) As HomePhone
                    , Min ( Case When PN.PhoneNumberType = 'Work' Then UPN.PhoneNumber End ) As WorkPhone
                    , Min ( Case When PN.PhoneNumberType = 'Fax' Then UPN.PhoneNumber End ) As FaxPhone
                From UserPhoneNumbers As UPN
                        Join    (
                                Select Min(UPN1.UserPhoneNumberId) As MinUserPhoneNumberId
                                    , UPNT1.PhoneNumberType
                                From UserPhoneNumbers As UPN1
                                    Join UserPhoneNumberTypes As UPNT1
                                        On UPNT1.UserPhoneNumberTypeID = UPN1.UserPhoneNumberTypeID
                                Where UPNT1.PhoneNumberType In('Home', 'Work', 'Fax')
                                Group By UPN1.UserID, UPNT.PhoneNumberType
                                ) As PN
                            On PN.MinUserPhoneNumberId = UPN.UserPhoneNumberId
                Group By UPN.UserId
                ) As PhoneNumbers
    On PhoneNumbers.UserId = Users.UserId

Dans cette solution, pour chaque utilisateur et le type de numéro de téléphone, je vais chercher la plus faible valeur de clé primaire de la UserPhoneNumbers table (j'ai deviné que la colonne a été nommé UserPhoneNumberId).

InformationsquelleAutor Thomas

Je suppose que vous avez quelque champ de clé primaire de chaque table jointe, depuis UserID n'est pas unique. Je vais supposer que votre clé primaire est appelée ID. Nous allons prendre les records avec le plus bas de l'ID. Cela répond à votre "première" des critères.

SELECT  Users.UserID, Users.FirstName, Users.LastName, hp.HomePhone,
        wp.WorkPhone, fn.FaxNumber
FROM Users
LEFT JOIN HomePhone hp ON hp.UserID = Users.UserID
LEFT JOIN HomePhone hp2 ON hp2.UserID = Users.UserID AND hp2.ID < hp.ID
LEFT JOIN WorkPhone wp ON wp.UserID = Users.UserID
LEFT JOIN WorkPhone wp2 ON wp2.UserID = Users.UserID AND wp2.ID < wp.ID
LEFT JOIN FaxNumber fn ON fn.UserID = Users.UserID
LEFT JOIN FaxNumber fn2 ON fn2.UserID = Users.UserID AND fn2.ID < fn.ID
WHERE hp2.ID IS NULL AND wp2.ID IS NULL AND fn2.ID IS NULL

Il y a un chapitre entier sur ce type de problème, appelé "Ambigu Gruops", dans le livre SQL Antipatterns.

InformationsquelleAutor Marcus Adams

0

Vous devez définir ce que vous entendez par "premier" quand il y a deux nombres de même type, puis ajouter une condition pour rejoindre, de sorte que seul le bon dossier répond aux critères. Il n'y a pas de raccourci pour cette.
- La première La première ligne renvoyée par l'instruction select. Il n'y a pas d'autres critères dans le tableau à la limite de la suite définie par.
InformationsquelleAutor Joel Coehoorn

Vous pouvez simplement utiliser GROUP BY:

SELECT  Users.UserID, 
  Users.FirstName, Users.LastName,
  HomePhone, WorkPhone, FaxNumber

FROM Users

LEFT JOIN
 (SELECT UserID, min(PhoneNumber) AS HomePhone
 FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
 WHERE UserPhoneNumberTypes.PhoneNumberType='Home'
 GROUP BY userID) AS tmpHomePhone
 ON tmpHomePhone.UserID = Users.UserID
LEFT JOIN
 (SELECT UserID, min(PhoneNumber) AS WorkPhone
 FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
 WHERE UserPhoneNumberTypes.PhoneNumberType='Work'
 GROUP BY userID) AS tmpWorkPhone
 ON tmpWorkPhone.UserID = Users.UserID
LEFT JOIN
 (SELECT UserID, min(PhoneNumber) AS FaxNumber
 FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
 WHERE UserPhoneNumberTypes.PhoneNumberType='Fax'
 GROUP BY userID) AS tmpFaxNumber
 ON tmpFaxNumber.UserID = Users.UserID

Au lieu de la fonction min(), vous pouvez utiliser le max() ainsi.

Ou vous pourriez le faire dans un groupe par:

SELECT  Users.UserID, 
  Users.FirstName, Users.LastName,
  max(HomePhone) as HomePhone,
  max(WorkPhone) as WorkPhone,
  max(FaxNumber) as FaxNumber

FROM Users

LEFT JOIN
 (SELECT UserID, PhoneNumber AS HomePhone
 FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
 WHERE UserPhoneNumberTypes.PhoneNumberType='Home') AS tmpHomePhone
 ON tmpHomePhone.UserID = Users.UserID
LEFT JOIN
 (SELECT UserID, PhoneNumber AS WorkPhone
 FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
 WHERE UserPhoneNumberTypes.PhoneNumberType='Work') AS tmpWorkPhone
 ON tmpWorkPhone.UserID = Users.UserID
LEFT JOIN
 (SELECT UserID, PhoneNumber AS FaxNumber
 FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
 WHERE UserPhoneNumberTypes.PhoneNumberType='Fax') AS tmpFaxNumber
 ON tmpFaxNumber.UserID = Users.UserID

InformationsquelleAutor Frank

0

Tiens, juste pour comprendre la question.

Vous ai deux tables:

Utilisateurs (nom d'utilisateur --> x) UserPhones (UserID, PHoneType --> Numéro de Téléphone)
et UserID /PhoneType n'est pas unique.

Tout d'abord il n'est pas nécessaire pour les tables temporaires:
```
Select 
 x
from
 Users
inner join 
 (
   Select 
    top 1 y
   from
    FoneTypes
   where
    UserID = users.UseriD
   and phoneType = 'typex'
 ) as PhoneTypex on phonetypex.UserID = users.userID
```
Ajouter des jointures internes si nécessaire.

Ou ai-je raté quelque chose?
- Ok, je crois que je vous comprends mieux maintenant... Est-il un champ ID sur le téléphone de table? Vous pourriez peut-être se joindre à un max(id), je sais que j'ai fait similaire avant
- Ceux-là ne sont pas vraiment les tables temporaires, juste des sous-requêtes, mais vous avez raison, vous n'avez pas besoin d'eux.
InformationsquelleAutor Gary

Vous devez vous connecter pour publier un commentaire.