title: How do I limit a LEFT JOIN to the 1st result in SQL Server?
body: <p>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. </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>This is for SQL Server 2000.</p>
  
  <p>Thanks,</p>
  
  <pre><code>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
  </code></pre>