0

I have one table called 'employees' with some data like employee id, employee name and manager id. I have self joined it to show the number of subordinates under each manager.

It looks like this and it works fine: SQL fiddle link

The table looks like this:

CREATE TABLE employees (
  emp_id INT,
  emp_name VARCHAR(16),
  mgr_id INT,

  );


INSERT INTO employees VALUES 
(1,'George',5),
(2,'Monica',5),
(3,'Rachel',5),
(4,'Marie',5),
(5,'Emma',6),
(6,'David',NULL);

And the query itself that shows the number of direct subordinates grouped by manager is this:

SELECT

  m.emp_name,
  COUNT(e.emp_name) AS number_of_subordinates

FROM

  employees as e
  JOIN employees as m
  ON e.mgr_id=m.emp_id

GROUP BY m.emp_name
ORDER BY number_of_subordinates DESC

However, this shows me the direct subordinates of each manager. What I want to be able to show as well is the number of indirect subordinates. So, for example, whilst Emma would still have 4 subordinates, David would have the 4 Emma has plus Emma which equals 5 (since he's Emma's boss and Emma is the boss of George, Monica, Rachel and Marie.

user3552829
  • 103
  • 1
  • 4
  • 13
  • Please post your query into your question itself. If the linked SQLFiddle goes stale, this question becomes worthless to future visitors (edit: thank you) – Bojangles Oct 05 '14 at 20:51
  • 2
    You need to do a recursive or hierarchical query. MySQL has basically no support for these queries. To do what you want, you need to use a stored procedure (or change the data structure or use a different database engine). – Gordon Linoff Oct 05 '14 at 20:52
  • 1
    If you just want a set depth, say only sub-ordinates up to 2 levels, you can just do a join for each depth. However, if you want it to work for all trees, you need a recursive query, which MySQL doesn't support out of the box. – wvdz Oct 05 '14 at 20:55

1 Answers1

1

You're looking for a recursive self join. This is not supported in MySQL, although it is in a number of other DBMSs (Oracle, Microsoft SQL Server, DB2, PostgreSQL etc.).

This answer provides a way to get around this, although it is not ideal, as described in its comments. Answers to this question discuss alternative ways of representing hierarchical data in MySQL.

Source: here

Community
  • 1
  • 1
gandaliter
  • 9,863
  • 1
  • 16
  • 23
  • Thanks!! I'm new to SQL so not sure how to do a recursive join :( And I need to be able to do this by tomorrow. Any volunteers to quickly show me how I can do this in this example? :) – user3552829 Oct 05 '14 at 21:03
  • You can't do one in MySQL. Unless you want to switch to another DBMS, or change your database structure, you'll probably have to use the method I suggested to get around it. See the answer I linked. – gandaliter Oct 05 '14 at 21:05
  • I am looking at that but struggling to understand how it would work in my case. Like I said, I'm a newbie and not quite good at decoding stuff yet. – user3552829 Oct 05 '14 at 21:16