Help - 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

Help

Author

Topic

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-12-31 : 01:18:23

Hi!

This is sample data from my database

create table sqlemp (empid int,empname varchar(100),managername varchar(100))

insert sqlemp values(100,'raja',null)
insert sqlemp values(101,'ram','100')
insert sqlemp values(102,'priya','100')
insert sqlemp values(103,'latha','102')

select * from sqlemp

empid empname managerid
------ ------- ---------
100 raja null

101 ram 100

102 priya 100

103 latha 102

I want the output like

empid empname managername
------ ------- -----------
100 raja null

101 ram raja

102 priya raja

103 latha priya

How can i acheive this?

I've tried various joins. I think self join is used to achieve this.
but i dont know how to do?

Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 01:38:48

[code]SELECT t1.empid,t1.empname,t2.empname as 'managername'
FROM sqlemp t1
LEFT OUTER JOIN sqlemp t2
ON t1.managerid = t2.empid[/code]

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-12-31 : 01:58:46

Hi your query works for one level only. You should use CTE to get all levels of data.
Do forum/google search on CTE's.

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-12-31 : 02:05:07

Hi!

Thanks Visakh !Thank You so much!

SELECT t1.empid,t1.empname,t2.empname as 'managername'
FROM sqlemp t1
LEFT OUTER JOIN sqlemp t2
ON t1.managername = t2.empid

kiruthika!
http://www.ictned.eu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 02:15:49

You had given field name as managerid in the sample o/p. Thats why i used managerid. Also the name managerid makes more sense as you are storing only an id in it. Also make sure you cast it to int type in join statement as i find empid to be of type int and managername to be type varchar.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources