Self Join SQL 1

[robertfah]

I've got 2 tables that I need to get data from...my users table looks like this:

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
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 & 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:

2, Mark, Smith
3, Tim, Stone

Any ideas?

 

[SQLScholar]

What your asking for has no need for a self join - at the end of the day you only need two tables.

Try this:

SELECT
*
from
[UsersAlternate Table Data] Alt
inner join
[Users Table Data] users
on
alt.AlternateUserID = users.UserID

WHERE alt.UserID = 1

unless your requirements have changed that should work. Any problems let me know.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[robertfah]

SQLScholar - thanks a bunch, works like a charm!