| Author |
 Topic  |
|
|
raysefo
Constraint Violating Yak Guru
257 Posts |
 Posted - 11/09/2012 : 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 ApproveAuthority
John Jack 0
Jack Jill 1
Susan Josh 0
Josh Terry 1
Terry Hans 2
Jill Jill 4
Hans 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/09/2012 : 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 @Table
VALUES
('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,Manager
FROM cte
where SAMAccountName = 'John'
OPTION (Maxrecursion 0)
Remove 'jill','Jill',4 from your dataset and it will run
Jim
P.S. It would be helpful if in the future , you provided this
DECLARE @Table TABLE(SAMAccountName varchar(20),Manager varchar(20),ApproveAuthority tinyint)
INSERT INTO @Table
VALUES
('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
257 Posts |
Posted - 11/09/2012 : 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
257 Posts |
Posted - 11/10/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/10/2012 : 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 @Table
VALUES
('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
USA
643 Posts |
Posted - 11/11/2012 : 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 |
|
|
| |
Topic |
|
|
|
How to Query This???
