Query of two FK both related with same table

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query of two FK both related with same table

Author

Topic

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-03-23 : 21:46:49

Hi I'm trying to build a query that related two FK both with the same table, or in another words, related each other. Per example: in a forum like we have here in SqlTeam, in each thread have the person who made it and the last person who posted in it. Well, in data base that I'm building, there is a table for user’s details and a table of threads. In the table of threads have two columns, one of them says who created the thread and another saying who made the last post, but the both are just the ids. So, how do I do to get their respective nicks in the user's table? The following code says what I pretend to do.

SELECT threads.*, users.nick
FROM threads INNER JOIN users ON threads.last_user_id = users.user_id
INNER JOIN users ON threads.user_id = users.user_id

I know that I'm quite far from what I want. I think that I have to create a CTE and replace the columns, but I don't know how to do it.

Thank you very much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-23 : 22:20:07

use table alias


SELECT t.*, u.nick, l.nick
FROM   threads t 
INNER JOIN users u ON t.user_id      = u.user_id
INNER JOIN users l ON t.last_user_id = l.user_id

rsegecin
Yak Posting Veteran

82 Posts

Posted - 2007-03-24 : 09:51:50

Just forgot about rename the columns, but the important part you did.

Thank you very much Khtan.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources