Employee Hierarchy

Question

Post reply

Employee Hierarchy

UnionAll

SSCrazy

Points: 2102
More actions
August 30, 2012 at 5:29 pm

#266792

There is a table similar to the one below:
create table #Orders
(
OrdNum varchar(5)
, Empid int
, Mgrid int
, MgrLevel int
)
insert #Orders
values('XZ1', 100, 100, 1)
, ('XZ1', 100, 351, 2)
, ('XZ1', 100, 355, 3)
, ('XZ1', 200, 200, 1)
, ('XZ1', 200, 451, 2)
, ('XZ1', 200, 555, 3)
, ('SY1', 200, 200, 1)
, ('SY1', 200, 451, 2)
, ('SY1', 200, 555, 3)
I need a query that takes order number and empid as parameters and returns all the employees involved in an order, so for example, for order XZ1 and employee 555, result should be 200, 451, and 555.
Thanks in advance for your help.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 1

This is probably too simple, but maybe a start for you:

SELECT a.*

FROM #Orders a

INNER JOIN (

SELECT OrdNum, EmpID

FROM #Orders

WHERE OrdNum = 'XZ1' AND Mgrid = 555

) b

ON a.OrdNum = b.OrdNum and a.EmpID = b.EmpID

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Please see the following. It's pretty simple to do once you know how...

http://www.sqlservercentral.com/articles/T-SQL/72503/

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

Jeff Moden (8/30/2012)
Please see the following. It's pretty simple to do once you know how...
http://www.sqlservercentral.com/articles/T-SQL/72503/

I was initially expecting to have to use the standard rCTE adjacency list traversal, but then it seemed the OPs requirements were a bit simpler. That why I said it looked "too easy."

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

dwain.c (8/30/2012)
Jeff Moden (8/30/2012)
Please see the following. It's pretty simple to do once you know how...
http://www.sqlservercentral.com/articles/T-SQL/72503/
I was initially expecting to have to use the standard rCTE adjacency list traversal, but then it seemed the OPs requirements were a bit simpler. That why I said it looked "too easy."

Understood and, to be sure, I wasn't knocking you. I was just offering an alternative.

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

UnionAll SSCrazy Points: 2102 More actions · Answer 5

Thank you all for the feedback. I ended up using dwain.c's query. Other articles you provided are very clear too. There are a few problems i can easily solve using these. Thanks again.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

You bet... thank you for the feedback.

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 7

Jeff Moden (8/31/2012)
dwain.c (8/30/2012)
Jeff Moden (8/30/2012)
Please see the following. It's pretty simple to do once you know how...
http://www.sqlservercentral.com/articles/T-SQL/72503/
I was initially expecting to have to use the standard rCTE adjacency list traversal, but then it seemed the OPs requirements were a bit simpler. That why I said it looked "too easy."
Understood and, to be sure, I wasn't knocking you. I was just offering an alternative.

I didn't think so.

And to OP: You're welcome, glad it works for you.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Smash125 Hall of Fame Points: 3562 More actions · Answer 8

Smash125

Hall of Fame

Points: 3562

September 2, 2012 at 4:42 am

#1532717

Good article Jeff Moden Helped me !!!!

Smash125 Hall of Fame Points: 3562 More actions · Answer 9

Can be solved using DECLARE and derived column

DECLARE @OrdNum varchar(5)

DECLARE @Empid int

SET @OrdNum='XZ1'

SET @Empid=200

SELECT * FROM (SELECT OrdNum,Empid,Mgrid,MgrLevel FROM #Orders

WHERE OrdNum=@OrdNum AND Empid=@Empid

) AS A

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

Smash125 (9/2/2012)
Can be solved using DECLARE and derived column
DECLARE @OrdNum varchar(5)
DECLARE @Empid int
SET @OrdNum='XZ1'
SET @Empid=200
SELECT * FROM (SELECT OrdNum,Empid,Mgrid,MgrLevel FROM #Orders
WHERE OrdNum=@OrdNum AND Empid=@Empid
) AS A

Heh... funny enough, that works for the data given. The given data isn't actually hierarchical data (yet).

You don't need the outer SELECT, though.

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

Smash125 Hall of Fame Points: 3562 More actions · Answer 11

Smash125

Hall of Fame

Points: 3562

September 2, 2012 at 7:49 am

#1532726

Just curious to know that i am not getting it

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

Smash125 (9/2/2012)
Just curious to know that i am not getting it

NP. The following will do the exact same thing as your query. All I did was remove the outer SELECT.

DECLARE @OrdNum varchar(5)

DECLARE @Empid int

SET @OrdNum='XZ1'

SET @Empid=200

SELECT OrdNum,Empid,Mgrid,MgrLevel FROM #Orders

WHERE OrdNum=@OrdNum AND Empid=@Empid

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

Smash125 Hall of Fame Points: 3562 More actions · Answer 13

Smash125

Hall of Fame

Points: 3562

September 2, 2012 at 8:57 am

#1532731

.:-)