Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Huligan
Yak Posting Veteran
66 Posts |
 Posted - 2003-04-08 : 16:56:02
|
| The problem I'm having is I need to call a single table with multiple joins that can not be related. I'll use the the thread view of this message board as an example. When viewing all threads for a category, each thread has an author and the user who last posted. Imagine that the names of the author and the last poster are not in the table that houses the threads (tblThreads). tblThreads just has the keys for those users and the names are stored in tblUsers. So to get the names of the author and the last poster I need joins to tblUsers twice.I would like to find a solution to this problem without changing the database structure if possible. Also, it is very important that I can change the name of a user in one place.Thank you for your time.Les |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-08 : 17:17:22
|
| you can join to the same table as many times as you need to in a SELECT statement, you just need to use an alias to distinguish between them.selet topics.*, author.Name, Poster.Namefromtopicsinner join Users as Author on topics.AuthorID = users.userIDinner join Users as Posteron topics.PosterID = users.UserIDTry it out, play around, you'll get the hang of it. Note that the "AS" is optional; lots of times people just write:inner join users AuthorIt can be very confusing when you are first learning SQL to have any idea what that means.I hope this helps!- Jeff |
 |
|
|
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2003-04-08 : 17:21:22
|
| I made the mistake of not using an alias with both tables. Thanks again for your help.Les |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-08 : 18:28:31
|
| ooops... you probably figured it out already, but i should have written:select topics.*, author.Name, Poster.Name from topics inner join Users as Author on topics.AuthorID = Author.userID inner join Users as Poster on topics.PosterID = Poster.UserID Glad to help .. hope that didn't mess you up too much!- Jeff |
|
|
|
|
|
|
Using same table in unrelated joins in 1 query