How to query in the same table

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)

How to query in the same table

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-05-19 : 09:18:48

I've as follow,
declare @t1 table (EmployeeID int, FullName varchar(100), ManagerID int);
insert into @t1 values (4, 'Pete Pop', 3)

insert into @t1 values (3, 'Andrew Jack', 1)

insert into @t1 values (1, 'John Smith', null)

insert into @t1 values (7, 'Cate Aniston', 3)

The ManagerID is the EmployeeID of the employee’s manager

How SQL look's like to get result as follow,

EmployeeID  EmployeeFullName     ManagerFullName

----------- -------------------- --------------------

4           Pete Pop             Andrew Jack

3           Andrew Jack          John Smith

1           John Smith           No manager

7           Cate Aniston         Andrew Jack

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-19 : 09:27:22

Assuming the familiar emp table i have written the query below.
this shows the desired o/p

select e.empid,e.name,m.empid as manager
from emp e join emp m on e.mgrid = m.empid

empid name managerid
2 Peter 1
3 Sam 1
4 Henry 2

hope this helps....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-19 : 09:27:39

Look for examples on CTE.
And please don't replace your post by 'gre' or 'ffg'

No, you're never too old to Yak'n'Roll if you're too young to die.

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-19 : 09:29:36

this is just for avoiding confusion:
the data i have is like below:
empid---name-----managerid
1------- John---- NULL
2------- Peter--- 1
3------- Sam----- 1
4------- Henry--- 2

John is in the highest position so he doesnt have mgrid

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-05-19 : 09:39:07

you're rite webfred

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources