0

I have a single directory table that contains employees and their immediate managers. I'm trying to loop up through the reporting hierarchy to return everyone up the reporting line as far as necessary - when you hit someone with no manager (i.e the CEO) then you can stop. Rather than looping through a bunch of PHP if/else statements, I'm trying to do it in one MySQL query.

Best effort so far has been:

SELECT d1.empno AS d1, d2.empno AS d2, d3.empno AS d3, d4.empno AS d4, d5.empno AS d5, d7.empno AS d7, d8.empno AS d8
FROM `directory` AS d1
LEFT JOIN directory AS d2 ON d1.manageremp = d2.empno
LEFT JOIN directory AS d3 ON d2.manageremp = d3.empno
LEFT JOIN directory AS d4 ON d3.manageremp = d4.empno
LEFT JOIN directory AS d5 ON d4.manageremp = d5.empno
LEFT JOIN directory AS d6 ON d5.manageremp = d6.empno
LEFT JOIN directory AS d7 ON d6.manageremp = d7.empno
LEFT JOIN directory AS d8 ON d7.manageremp = d8.empno
WHERE d1.empno = '38414'

but this returns all eight results regardless of whether it's necessary. So if I'm retrieving the hierarchy for someone two steps below the CEO I get

d1     d2     d3    d4   d5   d6   d7   d8
13307  55192  10917 NULL NULL NULL NULL NULL

Whereas I really only want to get this:

d1     d2     d3
13307  55192  10917

So I only want to select the next empno if the previous manageremp was NOT NULL. But stuff like this doesn't seem to work:

SELECT d1.empno AS d1, 
CASE 
WHEN d1.manageremp IS NOT NULL THEN d2.empno END AS d2, 
CASE 
WHEN d2.manageremp IS NOT NULL THEN d3.empno END AS d3,
CASE 
WHEN d3.manageremp IS NOT NULL THEN d4.empno END AS d4,
...

I'm sure there's an elegant solution to this but I'm not seeing it. Any ideas?

Toby Nieboer
  • 86
  • 4
  • 11
  • 1
    Sorry, but there's no way around that. You can't conditionally change the column count within the query. If you find this query does what you need then it'll be easy enough to ignore the nulls when you process the result using PHP, plus you'll be doing yourself a favor on the PHP end if you send back a predictable number of columns. – Ed Gibbs May 03 '13 at 02:41
  • You can of course **coalesce** the `NULL` values to blanks or spaces, to improve readability. – Pieter Geerkens May 03 '13 at 03:50
  • yeah seems a lot easier if you return the non-null rows and then use your PHP to transpose it http://stackoverflow.com/questions/3460022/is-there-better-way-to-transpose-a-php-2d-array – gillyspy May 03 '13 at 03:50

0 Answers0