mysql how to find the total number of child rows with respect to a parent

Question

I have a table which having parent child relatiionship like this,

Employee_ID     |  Employee_Manager_ID   |  Employee_Name
--------------------------------------------------------
1               |  1                    |  AAAA
2               |  1                    |  BBBB
3               |  2                    |  CCCC
4               |  3                    |  DDDD
5               |  3                    |  EEEEE

Is it possible to get the count of all the employees come under a particular employee(Not only direct child,count of all the childs of child ) using a single query ?

Eg if the input  = 1
output should be 4

if input  = 2 ,output should be  3

thanks in advance

Answer 1

Suppose your table is:

mysql> SELECT * FROM Employee;
    +-----+------+-------------+------+
    | SSN | Name | Designation | MSSN |
    +-----+------+-------------+------+
    | 1   | A    | OWNER       | 1    |
    | 10  | G    | WORKER      | 5    |
    | 11  | D    | WORKER      | 5    |
    | 12  | E    | WORKER      | 5    |
    | 2   | B    | BOSS        | 1    |
    | 3   | F    | BOSS        | 1    |
    | 4   | C    | BOSS        | 2    |
    | 5   | H    | BOSS        | 2    |
    | 6   | L    | WORKER      | 2    |
    | 7   | I    | BOSS        | 2    |
    | 8   | K    | WORKER      | 3    |
    | 9   | J    | WORKER      | 7    |
    +-----+------+-------------+------+
    12 rows in set (0.00 sec)

Query is:

SELECT  SUPERVISOR.name AS SuperVisor, 
        GROUP_CONCAT(SUPERVISEE.name  ORDER BY SUPERVISEE.name ) AS SuperVisee, 
        COUNT(*)  
FROM Employee AS SUPERVISOR 
  INNER JOIN Employee SUPERVISEE ON  SUPERVISOR.SSN = SUPERVISEE.MSSN 
GROUP BY SuperVisor;

The query will produce result like:

    +------------+------------+----------+
    | SuperVisor | SuperVisee | COUNT(*) |
    +------------+------------+----------+
    | A          | A,B,F      |        3 |
    | B          | C,H,I,L    |        4 |
    | F          | K          |        1 |
    | H          | D,E,G      |        3 |
    | I          | J          |        1 |
    +------------+------------+----------+
    5 rows in set (0.00 sec)

[Answer]: This for One level (immediate supervise) to find all supervises at all possible level you have to use while loop (use stored procedures).

Although it is possible to retrieve employees at each level and then take their UNION, we cannot, in general, specify a query such as "retrieve the supervisees of a employee at all levels" without utilizing a looping mechanism."

REFERENCE: in this slide read slid number 23. The BOOK is " FUNDAMENTALS OF FourthEdition DATABASE SYSTEMS" in chapter "The Relational Algebra and Relational Calculus" there is a topic "Recursive Closure Operations".

---

Adding Query for Table creation, May be helpful to you:

mysql> CREATE TABLE IF NOT EXISTS `Employee` (
    ->   `SSN` varchar(64) NOT NULL,
    ->   `Name` varchar(64) DEFAULT NULL,
    ->   `Designation` varchar(128) NOT NULL,
    ->   `MSSN` varchar(64) NOT NULL, 
    ->   PRIMARY KEY (`SSN`),
    ->   CONSTRAINT `FK_Manager_Employee`  FOREIGN KEY (`MSSN`) REFERENCES Employee(SSN)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.17 sec)

You can check Table like:

mysql> DESCRIBE Employee;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| SSN         | varchar(64)  | NO   | PRI | NULL    |       |
| Name        | varchar(64)  | YES  |     | NULL    |       |
| Designation | varchar(128) | NO   |     | NULL    |       |
| MSSN        | varchar(64)  | NO   | MUL | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)  

Answer 2

You may try this:

    SELECT
      table_name.Employee_ID,
      table_name.Employee_Name,
      COUNT(*) AS children
    FROM
      table_name AS t_one
    INNER JOIN table_name AS t_two ON
      t_two.Employee_Manager_ID=t_one.Employee_ID
    GROUP BY
      t_one.Employee_ID