How to Query This??? - 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

How to Query This???

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources