Author |
Topic |
raysefo
Constraint Violating Yak Guru
260 Posts |
Posted - 2012-11-09 : 08:40:24
|
Hi,I have a Users table. In this table there are some users with SAMAccountName,approveauthority and manager data.Here is a dummy data:SAMAccountName Manager ApproveAuthorityJohn Jack 0Jack Jill 1Susan Josh 0Josh Terry 1Terry Hans 2Jill Jill 4Hans Jill 3 I would like to get all of the Managers of lets say John. As you can see from the table above, Jack is the first manager of John.Jill is the second manager of John. Is there a way to retrieve this with select statement?PS One person has only one manager.best Regards. |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-09 : 11:33:48
|
You colud do this recursively, but since Jill is managed by herself, the query will run forever.DECLARE @Table TABLE(SAMAccountName varchar(20),Manager varchar(20),ApproveAuthority tinyint)INSERT INTO @TableVALUES('John','Jack',0),('Jack','Jill',1),('Susan','Josh',0),('Josh','Terry',1),('Terry','Hans',2), ('Jill','Jill',4),('Hans','Jill',3);with cte as ( SELECT SAMAccountName,Manager FROM @table UNION ALL SELECT t1.SAMAccountName,cte.Manager FROM @table t1 INNER JOIN cte on t1.Manager = cte.SAMAccountName)SELECT DISTINCT SAMAccountName,ManagerFROM cte where SAMAccountName = 'John'OPTION (Maxrecursion 0)Remove 'jill','Jill',4 from your dataset and it will runJimP.S. It would be helpful if in the future , you provided thisDECLARE @Table TABLE(SAMAccountName varchar(20),Manager varchar(20),ApproveAuthority tinyint)INSERT INTO @TableVALUES('John','Jack',0),('Jack','Jill',1),('Susan','Josh',0),('Josh','Terry',1),('Terry','Hans',2), ('Jill','Jill',4),('Hans','Jill',3)You'll get faster responses.Everyday I learn something that somebody else already knew |
|
|
raysefo
Constraint Violating Yak Guru
260 Posts |
Posted - 2012-11-09 : 15:01:01
|
Hi jimf,Thanks for your reply. I can NOT remove Jill from the table. Is there another way to do this?By the way, there are some entries without manager information. (Actually this table is from Active Directory. ) |
|
|
raysefo
Constraint Violating Yak Guru
260 Posts |
Posted - 2012-11-10 : 02:31:34
|
I can hanlde the entries without manager info. But how can I get the all managers of an employee? (In my example Jill is the General Manager so Jill has no manager and I can not change this) |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-10 : 08:00:47
|
Borrowing Jim's sample data, add a condition to exclude the case where manager and samaccountname are the same:DECLARE @Table TABLE(SAMAccountName varchar(20),Manager varchar(20),ApproveAuthority tinyint)INSERT INTO @TableVALUES('John','Jack',0),('Jack','Jill',1),('Susan','Josh',0),('Josh','Terry',1),('Terry','Hans',2),('Jill','Jill',4),('Hans','Jill',3);WITH cte AS( SELECT * FROM @Table WHERE SAMAccountName = 'John' UNION ALL SELECT t.SAMAccountName,t.Manager, t.ApproveAuthority FROM @table t INNER JOIN cte c ON c.Manager = t.SAMAccountName WHERE c.Manager <> c.SAMAccountName)SELECT * FROM cte; |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2012-11-11 : 17:59:57
|
There is nothing in the example data to quarantee the order of the rows. You cannot and must not rely on any form of natural order. Without some other column to guarantee the correct order or relationships, even if you come up with something that looks like it will work, it will eventually fail and it will do so without your knowledge... well... at least not until the users complain.--Jeff Moden |
|
|
|
|
|