Hierarchical Query in MySQL. (connect by equivalent for MySQL)

Question

I have three fields in a table that define a hierarchical relationship present in a MySQL database.

Table Name : tb_corp
--------------------------------------------
  comp_code  | incharge_comp_Code | mngr_emp_no

     A       |                    |    111
--------------------------------------------
     B       |          A         |  
--------------------------------------------
     C       |          B         |    
--------------------------------------------

How do I write a query to obtain all the comp_code that mngr_emp_no = 111 is in charge. According to the table above, 111 is in charge of three companies(A, B, and C). The reason is that A company is in charge of B company and B company is in charge of C company as a result A is also in charge of C company. (A -> B) (B -> C) == (A -> C)

This simple hierarchical model doesn't really lend itself to the kind of analysis you wan to do. An adjacency list would make it simple. See also http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database — symcbean, Feb 16 '17 at 12:27
@symcbean: The model shown *is* an adjacency list. So I'm not understanding your comment that... an "adjacency list would make it simple." Many database do support hierarchical queries of an adjacency list. Pretty simple in Oracle, using `CONNECT BY`. A little more convoluted in SQL Server with recursive CTE. But MySQL doesn't have equivalent of either of these features. — spencer7593, Feb 20 '17 at 22:14

score 0 · Accepted Answer · answered Feb 16 '17 at 05:14

There is no native hierarchical query support in MySQL.

For a finite number of levels to be traversed, we can write queries that get result for each level, and combine the results with a UNION ALL operator.

Or, we can write a MySQL stored program (procedure) for a more recursive approach.

As an example of approach using a native SQL query:

 SELECT t0.comp_code
   FROM tb_corp t0
  WHERE t0.mgr_emp_no = 111

 UNION ALL

SELECT t1.comp_code
  FROM tb_corp t0
  JOIN tb_corp t1 ON t1.incharge_comp_code = t0.comp_code
 WHERE t0.mgr_emp_no = 111

 UNION ALL

SELECT t2.comp_code
  FROM tb_corp t0
  JOIN tb_corp t1 ON t1.incharge_comp_code = t0.comp_code
  JOIN tb_corp t2 ON t2.incharge_comp_code = t1.comp_code
 WHERE t0.mgr_emp_no = 111

 UNION ALL

SELECT t3.comp_code
  FROM tb_corp t0
  JOIN tb_corp t1 ON t1.incharge_comp_code = t0.comp_code
  JOIN tb_corp t2 ON t2.incharge_comp_code = t1.comp_code
  JOIN tb_corp t3 ON t3.incharge_comp_code = t2.comp_code
 WHERE t0.mgr_emp_no = 111

etc. This approach can be extended to t4, t5, t6, ... down to some (reasonable) finite number of levels.

For a more recursive approach, a MySQL stored program (PROCEDURE) can be written.

Hierarchical Query in MySQL. (connect by equivalent for MySQL)

1 Answers1

Linked

Related