April 29, 2011 at 8:55 am
Hi all,
This is my first question in this forum. Please help me on following problem.
Emp Table contains following three fields
EmpID
ManagerID
EmpName
I need to show the Employee details after providing the EmpId. That I know how to do by writing a simple Select statement.
However, there may be circular references in the table. For a example, someone’s manager’s manager’s manager is him or herself. Now how to allow user to navigate from an employee’s record to that of his or her manager?
Please help me on this. Do I need to write a complex stored procedure to show or is there any ways to show that in a Select statement?
Please help...I already spent full day to show the circular reference of manager
Thanks
April 29, 2011 at 9:56 am
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 29, 2011 at 10:34 am
WayneS (4/29/2011)
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Thanks for your advice, I take your words as valuable points. Sorry for my mistake
This is Create statement
CREATE TABLE [dbo].[tblEmployee](
[EmpId] [char](10) NOT NULL,
[ManagerId] [char](10) NOT NULL,
[EmpName] [varchar](100) NULL
) ON [PRIMARY]
-----------------
These are Insert statement
INSERT INTO tblEmployee
(EmpId,ManagerId,EmpName)
VALUES ('Rs001','Rs003','John Smith')
--2
INSERT INTO tblEmployee
(EmpId,ManagerId,EmpName)
VALUES ('Rs002','Rs004','Mike Rao')
-- 3
INSERT INTO tblEmployee
(EmpId,ManagerId,EmpName)
VALUES ('Rs003','Rs008','Alex Smith')
--4
INSERT INTO tblEmployee
(EmpId,ManagerId,EmpName)
VALUES ('Rs004','Rs007','Parker Jordan')
--5
INSERT INTO tblEmployee
(EmpId,ManagerId,EmpName)
VALUES ('Rs006','Rs003','Michelle Lyda')
--6
INSERT INTO tblEmployee
(EmpId,ManagerId,EmpName)
VALUES ('Rs007','Rs007','Stephan George')
--7
INSERT INTO tblEmployee
(EmpId,ManagerId,EmpName)
VALUES ('Rs008','Rs008','Wicks Neel')
------------------------
output
Once you inserted these test data, you can see one employee's manager has another manager and that manager has another manager.
If user put this employee id Rs001 then it should show the managerid Rs008 (because Rs001' immediate manager is Rs003 and Rs003's manager is Rs008)
-----------
My work
I only know how to write "Select ManagerId, EmpName Where Empid='Rs001'
If I need to write this in VB.Net or C#, I know how to do. I can write a loop until I find the last point. However, I need this in T SQL (either Select statement or SP or Function)...
I feel like my head is spinning when I use my very little SQL knowledge to do this
----
I hope that now you or others can help me
April 29, 2011 at 12:43 pm
This is certainly a good start. Now what do you want the output to be?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 29, 2011 at 1:01 pm
The following code would get the entire hierarchy but I am not sure this is what you want.
declare @EmpID char(10)
set @EmpID = 'Rs001'
;with cte as (
select EmpID, ManagerId from tblEmployee where EmpID = @EmpID
union all
select e.EmpId, e.ManagerId
from tblEmployee e
inner join cte on cte.ManagerId = e.EmpId and e.EmpId <> e.ManagerId
)
select * from cte
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 29, 2011 at 2:04 pm
Sean Lange (4/29/2011)
The following code would get the entire hierarchy but I am not sure this is what you want.
declare @EmpID char(10)
set @EmpID = 'Rs001'
;with cte as (
select EmpID, ManagerId from tblEmployee where EmpID = @EmpID
union all
select e.EmpId, e.ManagerId
from tblEmployee e
inner join cte on cte.ManagerId = e.EmpId and e.EmpId <> e.ManagerId
)
select * from cte
Thanks a lot Sean Lange,
Got some good learning stuff from your script (I never know that I can give a name for block of script like you did (...;with cte as )
The output similar to my question. All what I want to see is when user input an Employee Id, it should show their superior manager (means manager's manager). In your code, it shows all of the managers 'Rs003' and 'Rs008' for employee Rs001. Instead of all, I need to show the last manager (top manager or superior manager, whatever terms applicable) which means, Rs008 only
I just started to learn Database stuff as I was working on Hardware, port related programming using C++, Python and C# and some simple VB.Net screen developments. Your every line of script is good info for me
Thanks again
April 29, 2011 at 2:16 pm
Glad that helped. That block of code is called a comm table expression, frequently referred to as a cte. To steal the line from Wayne's signature...
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Read up on ctes and understand how they work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 29, 2011 at 2:51 pm
You can add additional filtering to the statement after the cte. In your case, you need to identify the criteria that determines that you have reached the appropriate management level.
What that actual condition is - not sure...it could be as simple as a check for where the employee = manager (e.g. this employee does not have a manager, so stop here).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 29, 2011 at 5:29 pm
raj_paandy
The output similar to my question. All what I want to see is when user input an Employee Id, it should show their superior manager (means manager's manager). In your code, it shows all of the managers 'Rs003' and 'Rs008' for employee Rs001. Instead of all, I need to show the last manager (top manager or superior manager, whatever terms applicable) which means, Rs008 only
Try ammending Sean Lange's code by replacing
select * from cte with
select TOP(1)ManagerId FROM cte ORDER BY ManagerId DESC
Will the ammended code provide what you require?
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply