complex query - 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 2012 Forums

Transact-SQL (2012)

complex query

Author

Topic

wided
Posting Yak Master

218 Posts

Posted - 2014-12-23 : 03:41:15

Hello,

I have a table that contains this:
col1 col2

01 03
02 04
03
04 03
05 04
06 01
07 01
Exemple 03 is a manager of employees 01 et 04

Column 2 contains the line managers

if I am head main hierarchical, I need to see my employees and employees of my subordinates.

the head 03 should see the white collars 01 and 04 which are directly linked to it as well as 02 and 05 that are related to 04 itself linked to 03 ...
thanks

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-12-23 : 05:17:58

There are numerous examples of recursion most of which use employees.

eg
http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

wided
Posting Yak Master

218 Posts

Posted - 2014-12-23 : 07:52:01

thank you Ifor for your help,

the link you gave me gives me only the first level

for my example, the query only gives me employees 03 and its subordinate

I need to see the subordinate subordinate 03

manager of 04 =03
manager of 02 =04
i need to have if i log with 03:
03
01
04
02
05

thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-23 : 08:47:05

post your query.

wided
Posting Yak Master

218 Posts

Posted - 2014-12-23 : 09:40:37

i WANT A HELP: how to write my query to have a good result

if i put where col2 = 03
i need to have:

01 whose supervisor is 03
04 whose supervisor is 03
05 whose supervisor is 04 whose supervisor is 03
06 whose supervisor is 01 whose supervisor is 03
07 whose supervisor is 01 whose supervisor is 03

a recursive query

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-23 : 09:51:17

Adapt the example in

http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

to your database and see what you get. If its not what you want, post your adapted query for review.

wided
Posting Yak Master

218 Posts

Posted - 2014-12-23 : 10:05:59

I used a query from this link; but it only gives me the first level

only
line 01
line 04

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-24 : 08:54:15

Post the query you used!