Re-querying a table within the same query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

Re-querying a table within the same query?

Author

Topic

Rando
Starting Member

11 Posts

Posted - 2010-12-21 : 09:29:43

Hello,

I have a situation which I've not encountered before since I'm fairly new to SQL. I have a table that contains every single user within a building. Each user has a supervisor ID # that indicates who they report to. Since I have their ID # I would like to re-query the same table and get their name. How can I do this?

Table Data:


ID	Name		SupervisorID
12345	Susie Tester	98751
54321	Iam Testy	
54654	Tom McTest	
46546	Sammy Sample	54654
98751	Mark Debug	
65465 	Super Shopper	12345

Expected Results:


ID	Name		Supervisor
12345	Susie Tester	Mark Debug
54321	Iam Testy	
54654	Tom McTest
46546	Sammy Sample	Tom McTest
98751	Mark Debug	
65465 	Super Shopper	Susie Tester

I'm not too concerned with users not having a supervisor ID #. Thanks for any help

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-21 : 11:25:55

You can join a table to itself

Jim

DECLARE @table TABLE(id int,fName varchar(20),SupervisorID int)

INSERT INTO @Table

SELECT 12345,'Susie Tester', 98751 UNION ALL
SELECT 54321,'Iam Testy', null UNION ALL
SELECT 54654,'Tom McTest', null UNION ALL
SELECT 46546,'Sammy Sample', 54654 UNION ALL
SELECT 98751,'Mark Debug', null UNION ALL
SELECT 65465,'Super Shopper', 12345

SELECT t1.ID,t1.fName,[SuperVisor] = t2.fName
FROM @table t1
LEFT JOIN @table t2 ON t1.SuperviSorID = t2.ID

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources