How do I limit a LEFT JOIN to the 1st result in SQL Server?

Question

I have a bit of SQL that is almost doing what I want it to do. I'm working with three tables, a Users, UserPhoneNumbers and UserPhoneNumberTypes. I'm trying to get a list of users with their phone numbers for an export.

The database itself is old and has some integrity issues. My issue is that there should only ever be 1 type of each phone number in the database but thats not the case. When I run this I get multi-line results for each person if they contain, for example, two "Home" numbers.

How can I modify the SQL to take the first phone number listed and ignore the remaining numbers? I'm in SQL Server and I know about the TOP statement. But if I add 'TOP 1' to the LEFT JOIN select statement its just giving me the 1st entry in the database, not the 1st entry for each User.

This is for SQL Server 2000.

Thanks,

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 

Answer 1

Whenever you want to select only a top row from a left table for each row in the right table you should consider using the APPLY operator instead of join, and move the join condition inside the left join:

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 ... 

Answer 2

Assuming SQL Server 2005+, use 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 

Mind the what? placeholder for determining the first number. Omit the ORDER BY if you don't care at all...