SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to Query This???
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 11/09/2012 :  08:40:24  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
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
2869 Posts

Posted - 11/09/2012 :  11:33:48  Show Profile  Reply with Quote
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
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 11/09/2012 :  15:01:01  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
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. )
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 11/10/2012 :  02:31:34  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
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)
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/10/2012 :  08:00:47  Show Profile  Reply with Quote
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;
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 11/11/2012 :  17:59:57  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000