I don't know how to do it :(

Question

Post reply

I don't know how to do it :(

konstantinos.christodoulakis

SSC-Addicted

Points: 496
More actions
April 5, 2010 at 2:20 am

#213474

I have a table with the following columns:
EmployeeID
EmployeeName
EmployeeApproverID
EmployeeApproverID has (as value) one of the EmployeeID values as he/she is an employee too.
I want to make a select with the following results:
EmployeeID
EmployeeName
EmployeeApproverName (based on EmployeeID).
Any help will be approciated!
Best regards,
Konstantinos

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

You can acheive with a recursive CTE....

Here is a good example:

if object_id('Emp_Hierarchy') is not null

drop table Emp_Hierarchy

Create table Emp_Hierarchy

(

EmpId int identity(1,1) not null constraint pk_EmpId Primary key,

EmpName varchar(128) not null,

ReportingManager int constraint fk_rptMngr foreign key references Emp_Hierarchy(EmpId)

)

insert into Emp_Hierarchy (EmpName,ReportingManager )

select 'BOSS' , Null

union all

select 'BOD1' , 1

union all

select 'BOD2' , 1

union all

select 'BOD3' , 1

union all

select 'BOD4' , 1

union all

select 'Manager11' , 2

union all

select 'Manager12' , 2

union all

select 'Manager21' , 3

union all

select 'Manager22' , 3

union all

select 'Manager31' , 4

union all

select 'Manager32' , 4

union all

select 'Manager41' , 5

union all

select 'Manager42' , 5

select * From Emp_Hierarchy

;WITH HierarchyCTE (EmpID, EmpName , ReportingManager)

AS

(

SELECT EH.EmpID, EH.EmpName , EH.ReportingManager --, 0 LEVEL

FROM Emp_Hierarchy EH

WHERE EH.ReportingManager IS NULL

UNION ALL

SELECT EH.EmpID, EH.EmpName , EH.ReportingManager --, LEVEL + 1 LEVEL

FROM Emp_Hierarchy EH

INNER JOIN HierarchyCTE CTE ON EH.ReportingManager = CTE.EmpID

)

--SELECT * FROM HierarchyCTE ORDER BY EmpID

SELECT CTE.EmpID,

CTE.EmpName,

CASE WHEN Emp.EmpName IS NULL THEN 'Dude! This one is our BOSS'

ELSE Emp.EmpName

END AS Manager

FROM HierarchyCTE CTE

LEFT JOIN Emp_Hierarchy Emp ON Emp.EmpID = CTE.ReportingManager

ORDER BY EmpID

OPTION(MAXRECURSION 20)

Also, please post some sample data (insert statements), table structure (create table) so that ur request will be dealt faster...

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Please inform here if that code fixed your issue 🙂

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

This will also give u the result u wanted..

SELECT

EH.EmpID AS EmpID,

EH1.EmpName AS EmpName ,

EH.EmpName EmpApproverName

FROM Emp_Hierarchy EH

JOIN Emp_Hierarchy EH1

ON EH.EmpID = EH1.EmpApproverID

P.S: the highest employee's approver wont be appearing in that above query.

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 4

the article will gives u more info...CTEs and Trees

http://www.sqlservercentral.com/articles/Common+Table+Expression+(CTE)/61510/

🙂

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

the solution i have provided is created by me closely following the recursive-cte explanation give in BOL 2008 (http://msdn.microsoft.com/en-us/library/ms186243.aspx).. excellent article with the example walk-thro.. u ll find it very interesting..

also the article metioned by other poster was also an excellet one from frederic..

Happy Learning 🙂

konstantinos.christodoulakis SSC-Addicted Points: 496 More actions · Answer 6

More info:

Table name: MSP_RESOURCES

EmployeeID: RES_UID (PK, UID(uniqueidentifier)

EmployeeName: RES_NAME (NAME(nvarchar(255)),not null)

EmployeeApprover: RES_TIMESHEET_MGR_UID (UID(uniqueidentifier),null)

Do I need to create a new table and select from there?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

konstantinos.christodoulakis (4/5/2010)
More info:
Table name: MSP_RESOURCES
EmployeeID: RES_UID (PK, UID(uniqueidentifier)
EmployeeName: RES_NAME (NAME(nvarchar(255)),not null)
EmployeeApprover: RES_TIMESHEET_MGR_UID (UID(uniqueidentifier),null)
Do I need to create a new table and select from there?

You've been given a solution and some additional reading. Take the code provided and make appropriate changes based on your actual table. If you still have problems, let us know and we will help.

konstantinos.christodoulakis SSC-Addicted Points: 496 More actions · Answer 8

The solution is not working for some reason, so I'm still having problems.

I don't know why.

Any further help?

konstantinos.christodoulakis SSC-Addicted Points: 496 More actions · Answer 9

OK, I found the solution.

It's really a recursive query, but easier that you make it:

SELECT e1.RES_UID, e1.RES_NAME, e1.RES_TIMESHEET_MGR_UID, e2.RES_NAME AS TimesheetManagerName

FROM MSP_RESOURCES e1 LEFT JOIN MSP_RESOURCES e2 ON e1.RES_TIMESHEET_MGR_UID = e2.RES_UID

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

konstantinos.christodoulakis (4/5/2010)
OK, I found the solution.
It's really a recursive query, but easier that you make it:
SELECT e1.RES_UID, e1.RES_NAME, e1.RES_TIMESHEET_MGR_UID, e2.RES_NAME AS TimesheetManagerName
FROM MSP_RESOURCES e1 LEFT JOIN MSP_RESOURCES e2 ON e1.RES_TIMESHEET_MGR_UID = e2.RES_UID

Thanks for posting back that you found the answer.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 11

COldCoffee (4/5/2010)
This will also give u the result u wanted..
SELECT
EH.EmpID AS EmpID,
EH1.EmpName AS EmpName ,
EH.EmpName EmpApproverName
FROM Emp_Hierarchy EH
JOIN Emp_Hierarchy EH1
ON EH.EmpID = EH1.EmpApproverID
P.S: the highest employee's approver wont be appearing in that above query.

That will work for only 1 level of approval.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 12

konstantinos.christodoulakis (4/5/2010)
OK, I found the solution.
It's really a recursive query, but easier that you make it:
SELECT e1.RES_UID, e1.RES_NAME, e1.RES_TIMESHEET_MGR_UID, e2.RES_NAME AS TimesheetManagerName
FROM MSP_RESOURCES e1 LEFT JOIN MSP_RESOURCES e2 ON e1.RES_TIMESHEET_MGR_UID = e2.RES_UID

That's not a recursive query and it will only work for a single level.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 13

konstantinos.christodoulakis (4/5/2010)
More info:
Table name: MSP_RESOURCES
EmployeeID: RES_UID (PK, UID(uniqueidentifier)
EmployeeName: RES_NAME (NAME(nvarchar(255)),not null)
EmployeeApprover: RES_TIMESHEET_MGR_UID (UID(uniqueidentifier),null)
Do I need to create a new table and select from there?

It would be much more handy if you posted the relevant columns as a Create Table statement and posted some readily consumable data so people can test the code they make for you before they test it. See the article at the fist link in my signature line below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 14

Jeff Moden (4/5/2010)
That will work for only 1 level of approval.

Jeff i did not have the time to execute the query, my bad :doze:.. but the solution using the recursive query must work for any levels... this the OP did not try customizing the code for his needs...