title: SQL Self Join issues
body: <p>I've got 2 tables that I need to get data from...my users table looks like this:</p>
  
  <pre><code>Users Table
  -------------
  UserID
  FirstName
  LastName
  WebLogin
  WebPassword
  Active
  
  UserAlternates Table
  ---------------------
  UserAlternateID
  UserID
  AlternateUserID
  
  Users Table Data
  ------------------
  1, John, Brown, jbrown, jbrown, true
  2, Mark, Smith, msmith, msmith, true
  3, Tim, Stone, tstone, tstone, true
  
  UsersAlternate Table Data
  --------------------------
  1, 1, 2
  2, 1, 3
  3, 2, 1
  4, 3, 2
  5, 3, 1
  </code></pre>
  
  <p>The UserID refers back to the UserID in the Users table and so does the AlternateUserID.  This is a case where our program can have users that are "alternates" to other users.  So in the above example, if John Brown would have Mark &amp; Tim as Alternates, and Mark would have John as an alternate while Time would have Mark and John as alternates.  I'm drawing a blank on how to write the SQL to show the alternate users for a given userid.  So if I passed in UserID = 1, it would return:</p>
  
  <pre><code>2, Mark, Smith
  3, Tim, Stone
  </code></pre>
  
  <p>I tried this but it returns 2 rows of the same user data (in this case, 2 John Brown's):</p>
  
  <pre><code>CREATE      PROCEDURE [dbo].[GetUserAlternates]
  @UserID int
  
   AS
  SELECT u.FirstName, u.LastName, ua.AlternateUserID
  FROM Users u
  INNER JOIN UserAlternates ua ON u.UserID = ua.AlternateUserID
  WHERE u.UserID = @UserID
  </code></pre>
  
  <p>Any ideas? </p>