self join

Question

self join

vinoth-750003

SSC-Addicted

Points: 437
More actions
November 30, 2010 at 11:17 pm

#141570

hi,
This is my table..
create table Emp
(
empid int primary key,
empname varchar(50),
managerid int
)
select * from Emp
empid empname managerid
------- --------- --------
1000 mani 0
1001 kumar 1000
1003 arun 1000
1004 nataraj 1001
1005 kamal 1001
1006 raja 1003
1007 gopi 1003
1008 ram 1000
1009 siva 1000
1010 Sriram 1006
i want ouput like this
empid empname managerid
------- --------- --------
1000 mani 0
1001 kumar 1000
1004 nataraj 1001
1005 kamal 1001
1003 arun 1000
1006 raja 1003
1010 Sriram 1006
1007 gopi 1003
1008 ram 1000
1009 siva 1000
Please help me

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 1

What is the logic in getting the desired values, i havn't find any diffrence in your table and the out put other than some change in the order.

vinoth-750003 SSC-Addicted Points: 437 More actions · Answer 2

Should display all records from employee table and we need change the order of the records like this

1000 -->1001 --> 1004

--> 1005

--> 1003 -->1006 -->1010

--> 1007

-->1008

-->1009

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

I'm sorry, I don't understand the order that you're trying to achieve. It doesn't look like you're ordering by managerid or by employee id. Are you trying to retrieve the tree of employees under their manager?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

This is a horrible piece of code but it works:

DROP table #Emp

create table #Emp

(

empid int primary key,

empname varchar(50),

managerid int

)

INSERT INTO #Emp (empid, empname, managerid)

SELECT 1000, 'mani', 0 UNION ALL

SELECT 1001, 'kumar', 1000 UNION ALL

SELECT 1003, 'arun', 1000 UNION ALL

SELECT 1004, 'nataraj', 1001 UNION ALL

SELECT 1005, 'kamal', 1001 UNION ALL

SELECT 1006, 'raja', 1003 UNION ALL

SELECT 1007, 'gopi', 1003 UNION ALL

SELECT 1008, 'ram', 1000 UNION ALL

SELECT 1009, 'siva', 1000 UNION ALL

SELECT 1010, 'Sriram', 1006

;WITH Resolver AS (

SELECT [Level] = CAST(1 AS INT), empid, empname, managerid,

s1 = CAST(NULL AS INT),

s2 = CAST(NULL AS INT),

s3 = empid,

s4 = managerid

FROM #Emp

WHERE managerid = 0

UNION ALL

SELECT l.[Level]+1, n.empid, n.empname, n.managerid,

s1 = CASE WHEN l.[Level] = 4 THEN n.managerid WHEN l.[Level] = 3 THEN n.empid ELSE l.s1 END,

s2 = CASE WHEN l.[Level] = 3 THEN n.managerid WHEN l.[Level] = 2 THEN n.empid ELSE l.s2 END,

s3 = CASE WHEN l.[Level] = 2 THEN n.managerid WHEN l.[Level] = 1 THEN n.empid ELSE l.s3 END,

s4 = CASE WHEN l.[Level] = 1 THEN n.managerid ELSE l.s4 END

FROM Resolver l

INNER JOIN #Emp n ON n.managerid = l.empid

) SELECT empid, empname, managerid

FROM Resolver

ORDER BY s4, s3, s2, s1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

vinoth-750003 SSC-Addicted Points: 437 More actions · Answer 5

hi

Thanks for the response.

Our problem resolved.

by

VinothKumar.K

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

vinoth-750003 (12/1/2010)
hi
Thanks for the response.
Our problem resolved.
by
VinothKumar.K

I'd recommend that you hang around for a day or two. Often a piece of klunky code like this will be superceded by something far more efficient and easy to maintain.

Make it work

Make it fast

Make it pretty

You've got something which works, it shows it can be done, but it's not fast and it certainly isn't pretty.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden