Easy left join problem 4

[cristianivanoff]

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

 

[SgtJarrow]

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

 

[cristianivanoff]

Hello!
Thank you for your answer. Unfortunately its sql server 2000.

Br
cristian

 

[SgtJarrow]

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:

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

 

[wbodger]

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?

 

[markros]

In SQL 2000 you may implement recursion with a function. The maximum recursion level is 32.

PluralSight Learning Library

 

[Olaf Doschke]

If the tree of emplyoees and their managers will be shallow and stay shallow, I'd go for a plain sql like mstrmage1768 gave.

If you want to go with markros, here's an article about how to do recursion in SQL2000:

http://msdn.microsoft.com/en-us/library/aa175801(v=sql.80).aspx

Bye, Olaf.

 

[cristianivanoff]

Thank you all for all your replies!

The suggestion by mstrmage1768 worked fine!

Thanks!

Br
cristian