Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Easy left join problem 4

Status
Not open for further replies.

cristianivanoff

Technical User
Nov 13, 2006
43
SE
Hello all!

I have a table like this:

PersonID, PersonName, ManagerID
1, Joe, 45
2, Chris, 45
.....
.....
45, Marc, 99
...
...
99, Jessica, -


I would like a query that shows me:
PersonID, PersonName, ManagerID, ManagerName, Manager2_ID, Manager2_Name

I need to join the same table several times. How do I do that?

Br
cristian
 
You didn't mention what version of SQL you are using, but if you are using 2005 or newer, try searching for "recursive CTE". You will find this will do exactly what you are looking for...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer
 
Hello!
Thank you for your answer. Unfortunately its sql server 2000.

Br
cristian
 
OK...you should still be able to accomplish this with some self joins. It will just take a separate join for each level you want to show. Something like this:

Code:
DECLARE @TestTable TABLE
(
	PersonId	INT	PRIMARY KEY NOT NULL,
	PersonName	VARCHAR(50),
	ManagerId	INT
)

INSERT INTO @TestTable (PersonId, PersonName, ManagerId) VALUES (1, 'Joe', 45)
INSERT INTO @TestTable (PersonId, PersonName, ManagerId) VALUES (2, 'Chris', 45)
INSERT INTO @TestTable (PersonId, PersonName, ManagerId) VALUES (45, 'Marc', 99)
INSERT INTO @TestTable (PersonId, PersonName, ManagerId) VALUES (99, 'Jessica', NULL)

SELECT
	tt.PersonId 'PersonId',
	tt.PersonName 'PersonName',
	tt.ManagerId 'Manager1Id',
	m1.PersonName 'Manager1Name',
	m1.ManagerId 'Manager2Id',
	m2.PersonName 'Manager2Name'
FROM @TestTable tt
LEFT JOIN @TestTable m1
	ON tt.ManagerId = m1.PersonId
LEFT JOIN @TestTable m2
	On m1.ManagerId = m2.PersonId

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer
 
Are you saying that you basically have one table with all of your people and you need to show all of the parent/child (manager/employee) relationships within that table? And the number of levels that you need to provide will change depending on the level of the employee, yes?
 
Thank you all for all your replies!

The suggestion by mstrmage1768 worked fine!

Thanks!

Br
cristian
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top