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

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2010-01-18 : 10:15:13

in employee master:


empid empname mgrid
9102  senthil 1458
6006  soundar 1458
9027  mera    6006
6969  bala    1458
1458  srini   null

output should be:


sno  empid empname   mgrid level
1     1458  srini    null   1
2     6006  soundar  1458   2 
3     6969  bala     1458   2 
4     9027  meera    6006   3
5     9102  senthil  1458   2


using self join tried so far is:
select row_number() over (order by e1.empid)as sno,
       e1.empid,e1.empname,e2.mgrid
from  employee e1 left outer join employee e2
on (e1.mgrid=e2.empid)

i need to know,how to calculate level column
based on manager sequence no without dense_rank() function

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-01-18 : 10:46:43

You can use recursive CTE. The example on MSDN is very similar to the problem you are trying to solve. http://msdn.microsoft.com/en-us/library/ms186243.aspx

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-19 : 01:44:10

[code]
DECLARE @t TABLE (empid INT,empname VARCHAR(32),mgrid INT)
INSERT INTO @t SELECT
9102, 'senthil', 1458 UNION ALL SELECT
6006, 'soundar', 1458 UNION ALL SELECT
9027, 'mera', 6006 UNION ALL SELECT
6969, 'bala', 1458 UNION ALL SELECT
1458, 'srini', NULL

;WITH cte (empid,empname,mgrid,LEVEL)
AS
(
SELECT *,1 LEVEL FROM @t WHERE mgrid IS NULL
UNION ALL
SELECT e.empid,e.empname,e.mgrid,c.level+1 FROM @t e
JOIN cte c ON c.empid = e.mgrid
)

SELECT ROW_NUMBER()OVER(ORDER BY empid) AS sno, * FROM cte
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources