Root Record in Self Referencing Relation(Emp-MNGR)

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)

Root Record in Self Referencing Relation(Emp-MNGR)

Author

Topic

kunal1982
Starting Member

3 Posts

Posted - 2009-01-22 : 10:12:07

I have a table in which i have Employee Manager relationship (Self Referencing)

For Eg
Emp_ID Name ManagerID
001 E1 002
002 E2 003
003 E3 004
004 E4 NULL
005 E5 006
006 E6 NULL

Now i want to construct a view which shows Root (Parent manager of each employee) not just immediate manager. Actually Root is a department head of the department.
So each employee row should have a column for their respective Department Head

Like
Emp_ID Name Department Head
001 E1 004
002 E2 004
003 E3 004
004 E4 NULL
005 E5 006
006 E6 NULL

Any Idea how to go about this

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-22 : 10:18:29

look at cte concept

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 11:11:01

http://msdn.microsoft.com/en-us/library/ms186243.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 11:21:49

[code]
;With CTE(Emp_ID ,Name ,ManagerID)
AS
(
SELECT Emp_ID, Name, COALESCE(CAST(ManagerID AS varchar(max))+ ',','')
FROM Table
WHERE ManagerID IS NOT NULL
UNION ALL
SELECT t.Emp_ID, t.Name,COALESCE(CAST(t.ManagerID AS varchar(max))+',','')+ c.ManagerID
FROM CTE c
INNER JOIN Table t
ON t.Emp_ID=c.ManagerID
)

SELECT Emp_ID ,Name ,LEFT(ManagerID,CHARINDEX(',',MangerID)-1) AS [Department Head]
FROM CTE
[/code]

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-22 : 12:14:21

quote:

Originally posted by visakh16


;With CTE(Emp_ID ,Name ,ManagerID)
AS
(
SELECT Emp_ID, Name, COALESCE(CAST(ManagerID  AS varchar(max))+ ',','')
FROM Table
WHERE ManagerID IS NOT NULL
UNION ALL
SELECT t.Emp_ID, t.Name,COALESCE(CAST(t.ManagerID AS varchar(max))+',','')+ c.ManagerID
FROM CTE c
INNER JOIN Table t
ON t.Emp_ID=c.ManagerID
)

SELECT Emp_ID ,Name ,LEFT(ManagerID,CHARINDEX(',',MangerID)-1) AS [Department Head]
FROM CTE

Hi visakh, your cte is not giving what op wants

Actually op wants as

Emp_ID Name Department Head
001 E1 004
002 E2 004
003 E3 004
004 E4 NULL
005 E5 006
006 E6 NULL

which is mentioned earlier but your cte is giving o/p as

emp_id name department Head
001 E1 002
003 E3 004
005 E5 006
002 E2 003

where for emp_ids 001,002,003 should have departmenthead as 004 like that...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 12:21:04

ok...will test and post new solution tomm

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-23 : 02:37:44

[code]
Hi Try thiss

create function dbo.udf_cte
(
@empid int
)
returns @Table table (i int)
AS
begin
with cte(empid,name,managerid)
as
(
select empid,name,managerid from urtable where empid = @empid
union all
select t.empid,t.name,t.managerid from urtable t
inner join cte on (cte.managerid = t.empid)
)
insert into @Table
select empid from cte where managerid is null
return
end

select a.empid,a.name,NULLIF(b.i,a.empid) as managerid from urtable a
cross apply
(
select * from dbo.udf_cte(a.empid)
) b

Jai Krishna

[/code]

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-01-23 : 07:21:05

try like this

DECLARE @T TABLE 
(
		Emp_ID VARCHAR(10), 
		[Name] VARCHAR(10), 
		ManagerID VARCHAR(10)
)

INSERT INTO @T
			SELECT '001', 'E1', '002'
UNION ALL	SELECT '002', 'E2', '003'
UNION ALL	SELECT '003', 'E3', '004'
UNION ALL	SELECT '004', 'E4', NULL
UNION ALL	SELECT '005', 'E5', '006'
UNION ALL	SELECT '006', 'E6', NULL

; WITH Cte ( Emp_ID, [Name], ManagerId, [Path] )
AS
(
		SELECT	Emp_ID, [Name], ManagerId, CONVERT(VARCHAR(MAX), Emp_ID)
		FROM	@T
		WHERE	ManagerID IS NULL
		
		UNION ALL
		
		SELECT	t.Emp_ID,t.[Name],t.ManagerId, cte.[Path] + '-' + t.Emp_ID
		FROM	@T t
		INNER JOIN Cte Cte ON (t.ManagerId = cte.Emp_ID)
		
)

UPDATE T
SET ManagerId = LEFT(C.path, 3)
FROM @T T
INNER JOIN Cte C ON LEFT(C.path, 3) <> T.Emp_Id
		AND C.Path + '-' LIKE '%-' + T.Emp_Id + '-%'

SELECT * FROM @T

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources