join results from the same table

Question

I just can't find a solution to my problem:

I have a table employees with id's name and the id they report to.

Employees:
id   name  reports_to
2    name2    55
3    name3     2
4    name4     3
5    name5     3
6    name6     2
7    name7    33
8    name8    55

now I need to be able to select the person that reports to id 55, and all the person that report to this person (id2). But just one level down.

the result would look like this:

2    name2    55
3    name3     2
6    name6     2
8    name8    55

I tried joins and subquery but without success.

Edit: If possible the result should be ordered in a way my result table looks: - person that reports to id 55 (i.e. #2) - person that report to #2 should follow immediately

How come you expect user1 to be return? (Doesn't exist in table.) — jarlh, Dec 04 '15 at 12:04
sorry, names and id's got mixed up on copy. the relevant numbers are report_to — Delcon, Dec 04 '15 at 12:19

Gordon Linoff · Answer 1 · 2015-12-05T03:37:25.843

1

Here is one method, using a subquery to get the second level:

select e.*
from employees e
where e.reports_to = 55 or
      e.reports_to in (select e2.id from employees e2 where e2.reports_to = 55);

EDIT:

To get this sorted requires using join rather than in. I think the logic looks like this:

select e.*
from employees e left join
     employees e2
     on e.reports_to = e2.id
where 55 in (e.reports_to, e2.reports_to)
order by coalesce(e2.id, e.id), (e2.id is null) desc

edited Dec 05 '15 at 03:37

answered Dec 04 '15 at 12:03

Gordon Linoff

1,242,037
58
646
786

awesome! I have been trying for hours - and get an answer here in minutes! Is it possible to have the output sortet? I mean that the 2nd level reports_to is right after it's first level reports_to? The way it is now they do not follow the person they report to. – Delcon Dec 04 '15 at 12:48

score 0 · Answer 2 · answered Dec 04 '15 at 13:09

Or, if subqueries aren't really your thing...

SELECT y.* 
  FROM employees x 
  JOIN employees y 
    ON x.id IN(y.reports_to,y.id) 
 WHERE x.reports_to = 55;
+----+-------+------------+
| id | name  | reports_to |
+----+-------+------------+
|  2 | name2 |         55 |
|  3 | name3 |          2 |
|  6 | name6 |          2 |
|  8 | name8 |         55 |
+----+-------+------------+

Incidentally, I'd be interested if anyone knows why the query above formats the result set ever so slightly differently from the query below...

SELECT y.* 
  FROM employees x 
  LEFT 
  JOIN employees y 
    ON x.id IN(y.reports_to,y.id) 
 WHERE x.reports_to = 55;
+------+-------+------------+
| id   | name  | reports_to |
+------+-------+------------+
|    2 | name2 |         55 |
|    3 | name3 |          2 |
|    6 | name6 |          2 |
|    8 | name8 |         55 |
+------+-------+------------+
    ^^

Weird, hunh?

it does get me all the requested employees, but with the subquery, would it be possible to sort it in a way that the 2nd level report_to follow their 1st level report_to? — Delcon, Dec 04 '15 at 13:38

score 0 · Accepted Answer · edited May 23 '17 at 12:23

0

You have a hierarchy that has been modelled using the adjacency list model. The advantage of this model is that it is fairly intuitive to the modeller and facilitates updates for the user. However, the disadvantage, as you've discovered, it that it is not so easy to query because you need new a join (or equivalent) for each level in the hierarchy. This is particularly tricky when the number of levels is arbitrary.

For mysql solutions, see adjacency list vs nested set.

edited May 23 '17 at 12:23

Community

1
1

answered Dec 04 '15 at 15:12

onedaywhen

55,269
12
100
138

Thank you for pointing this out. And good links regarding this subject. – Delcon Dec 04 '15 at 16:51

join results from the same table

3 Answers3