Self Join - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Self Join

Author

Topic

asm
Posting Yak Master

140 Posts

Posted - 2007-10-24 : 02:06:16

I have a employee table which contain Code, Name, MgrCode
I want to display the hierarchy of employee under selected MgrCode

I try but unable to solve in single query

--DROP TABLE employee
--CREATE TABLE employee (Code varchar(5), Name varchar(50), MgrCode varchar(5))
--INSERT INTO [employee] ([Code],[Name],[MgrCode])VALUES('1','A',NULL)
--INSERT INTO [employee] ([Code],[Name],[MgrCode])VALUES('2','B','1')
--INSERT INTO [employee] ([Code],[Name],[MgrCode])VALUES('3','C','2')
--INSERT INTO [employee] ([Code],[Name],[MgrCode])VALUES('4','D','2')
--INSERT INTO [employee] ([Code],[Name],[MgrCode])VALUES('5','E','4')
--SELECT * FROM employee

Select Emp.Code as EmpCode,Emp.Name as EmpName,Emp.MgrCode,Mgr.Name as MgrName from Employee Emp
Left Join Employee Mgr On Emp.MgrCode=Mgr.Code Where Mgr.Code='1'

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 02:14:38

Are you using SQL Server 2000 or SQL Server 2005?

2000) Use WHILE LOOP
2005) Use CTE

E 12°55'05.25"
N 56°04'39.16"