Sub Query in Inner 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

Sub Query in Inner Join

Author

Topic

NadeemGul
Starting Member

4 Posts

Posted - 2007-06-04 : 17:27:30

Hi,
I need to write a query to get the data in hierarchy from a table.
Like emp table where each employee(EMPID) has an manager and manager is also an employee(EMPID).
There could be 2 level of managers. Mean an employee who is a manager can also have manager.
So i need to write query which shows three columns with column1=toplevel manager, column2=secondlevel manager and column3=team member.

Any help will be appreciated.

Thanks,
Nadeem

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-06-04 : 21:21:16

hi nadeem..
maybe u could provide some sample data so that the experts know what you are expecting.. like sample from the table, and the result u expect to get from the query :)..

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-05 : 03:52:24

See this: [url]http://www.nigelrivett.net/SQLTsql/RetrieveTreeHierarchy.html[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-05 : 09:16:26

Also read about Expanding Hierarchies in sql server help file

Madhivanan

Failing to plan is Planning to fail

NadeemGul
Starting Member

4 Posts

Posted - 2007-06-05 : 10:17:27

Here is some sample data
SOURCE
SELECT ELEMENTOWNERSEQ,MANAGERSEQ,NAME FROM CS_POSITION
RULEELEMENTOWNERSEQ MANAGERSEQ NAME
1001 2001 TEAM_MEMBER1
1002 2001 TEAM_MEMBER2
1003 2002 TEAM_MEMBER3
2001 3003 MGR1
2002 3003 MGR2
2003 3002 MGR3
2004 3003 MGR4
3001 NULL SECMGR1
3002 NULL SECMGR2
3003 NULL SECMGR3

REQUIRED:
SEC_LEVEL_MANAGER MANAGER TEAM_MEMBER
SECMGR1 NULL NULL
SECMGR2 2003 NULL
SECMGR3 2001 1001
SECMGR3 2001 1002
SECMGR3 2004 NULL

quote:
Originally posted by NadeemGul

Hi,
I need to write a query to get the data in hierarchy from a table.
Like emp table where each employee(EMPID) has an manager and manager is also an employee(EMPID).
There could be 2 level of managers. Mean an employee who is a manager can also have manager.
So i need to write query which shows three columns with column1=toplevel manager, column2=secondlevel manager and column3=team member.

Any help will be appreciated.

Thanks,
Nadeem

NadeemGul
Starting Member

4 Posts

Posted - 2007-06-05 : 10:27:45

SOURCE
EMPID MANAGERID NAME
1001 2001 TEAM_MEMBER1
1002 2001 TEAM_MEMBER2
1003 2002 TEAM_MEMBER3
2001 3003 MGR1
2002 3003 MGR2
2003 3002 MGR3
2004 3003 MGR4
3001 NULL SECMGR1
3002 NULL SECMGR2
3003 NULL SECMGR3

REQUIRED:
NAME MANAGERID EMPID
SECMGR1 NULL NULL
SECMGR2 2003 NULL
SECMGR3 2001 1001
SECMGR3 2001 1002
SECMGR3 2004 NULL

NadeemGul
Starting Member

4 Posts

Posted - 2007-06-05 : 12:31:04

I figured it out
FYI:

SOURCE
EmployeeID Name BossID
1001 Denis Eaton-Hogg NULL
1002 Bobbi Flekman 1001
1003 Ian Faith 1002
1004 David St. Hubbins 1003
1005 Nigel Tufnel 1003
1006 Derek Smalls 1003

SOLUTION
SELECT BigBoss.Name BigBoss, Boss.Name Boss, Employees.Name Employee
FROM Employees
INNER JOIN Employees AS Boss ON Employees.BossID=Boss.EmployeeID
INNER JOIN Employees BigBoss ON Boss.BossID=BigBoss.EmployeeID

RESULT
BigBoss Boss Employee
Denis Eaton-Hogg Bobbi Flekman Ian Faith
Bobbi Flekman Ian Faith David St. Hubbins
Bobbi Flekman Ian Faith Nigel Tufnel
Bobbi Flekman Ian Faith Derek Smalls

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-06-06 : 01:29:19

glad u help yourself :)

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources