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

SQL Server 2000 Forums

Transact-SQL (2000)

joins

Author

Topic

supersql
Yak Posting Veteran

99 Posts

Posted - 2005-10-06 : 15:02:30

Hi
I have an Employees table as
EmpId EmpNo SupervisorId
1 234 3
2 236 5
3 324 1

Here supervisorId is an EmpId where it is acting as supervisor for that EmpId.

I need empno's with a given EmpId and it shud by itself pickup the supervisordId and find the EmpNo for that EmpId.
For example, if i am given with EmpId=1, i need to get Empno's as
234
324
because for EmpId=1 we have SupervisorId=3 which is nothing but EmpId=3 and for that EmpId=3,we have Empno=324.

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-06 : 15:13:32

It called recursion, family trees, hierarchies.

2005 will have recursive queries. For now you'll need a loop or you can look at this http://www.sqlteam.com/item.asp?ItemID=8866

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

supersql
Yak Posting Veteran

99 Posts

Posted - 2005-10-06 : 15:24:03

looks like ur link is somethin different from what i have asked.

How c an we write a SP to do such job, do we need something like SELF JOINS?

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-06 : 15:57:44

Not really...

Here...for every UNION ALL, just keep adding a self join


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(EmpId int, EmpNo int, SupervisorId int)
GO

INSERT INTO myTable99(EmpId, EmpNo, SupervisorId)
SELECT 1, 234, 3 UNION ALL
SELECT 2, 236, 5 UNION ALL
SELECT 3, 324, 1
GO

   SELECT * 
     FROM myTable99 
    WHERE SupervisorID = 1
UNION ALL
   SELECT b.* 
     FROM myTable99 a 
     JOIN myTable99 b ON a.SupervisorId = b.EmpId 
    WHERE a.SupervisorID = 1
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-10-06 : 18:33:48

Man, I don't understand what exactly you do want.

supersql
Yak Posting Veteran

99 Posts

Posted - 2005-10-06 : 19:58:38

To be direct..I just need empno's of an empId and his supervisor,where only empId is given to me.

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-10-06 : 20:19:05

Here it is

select t.EmpNo as Employee, (case when tt.EmpNo is null then t.EmpNo else tt.EmpNo end) as Manger
from Employees t left join Employees tt on t.SupervisorId = tt.EmpId
where t.EmpId=1

It's simple but you drove me nut to figure it out what you want. Try to be clear next time.

Hippi

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-06 : 22:10:24

Why isn't EmpNo the pk of this table? Just curious.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-07 : 01:12:45

Refer more Tree structures here
http://www.nigelrivett.net/RetrieveTreeHierarchy.html
http://www.seventhnight.com/treestructs.asp

Madhivanan

Failing to plan is Planning to fail

supersql
Yak Posting Veteran

99 Posts

Posted - 2005-10-07 : 08:05:50

smith
even i have that doubt but its a database which is running from 5 yrs, and i am new to this environment, they just asked me to get such result so...

supersql
Yak Posting Veteran

99 Posts

Posted - 2005-10-07 : 08:22:19

Thanks Hippi
If i get you correct, I hope you are using a self join and a left outer join.

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-10-07 : 11:58:53

quote:
Originally posted by supersql

Thanks Hippi
If i get you correct, I hope you are using a self join and a left outer join.

Yeap, the reason that I use left join because if some employee is President, he is manager of himself.

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-07 : 12:16:38

Did you even try the code I gave you?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources