0

I have a table which lists all the employees in an org with their immediate parent.

id name parent type
1 A 0 1
2 B 0 1
3 C 1 2
4 D 2 2
5 E 3 3
6 F 4 3
7 E 5 4
8 F 6 4

I have another table which lists actions by last node employees:

id action type emp_id
1 Action Name 0 7
2 Action Name 0 8
3 Action Name 1 7
4 Action Name 2 7
5 Action Name 3 8
6 Action Name 4 8
7 Action Name 5 7
8 Action Name 6 8

I need to show a hierarchical view of the action count. As evident above, all the actions are created by either employee with an id of 7 or 8. I need to show the number of actions by employees with type 1 based on their own actions or by any sub down the chain.

At the moment, I pull the counts on the end node using sql and then use the scripting language to build the hierarchy bottom up.

Is there a better way to achive this with SQL?

Simel
  • 1
  • 1
    Does this answer your question? [How to create a MySQL hierarchical recursive query?](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – Charlieface Feb 07 '21 at 06:35
  • Have a look at these questions. https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/ https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/rchical-queries-in-mysql , https://dba.stackexchange.com/questions/46127/recursive-self-joins – vvs Feb 07 '21 at 06:40
  • What version are you using, and what would the result look like? Also, if the suggestions above don't solve your problem, see [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Feb 07 '21 at 13:38
  • At the very least, your question needs to show the results you want. Being explicit about the version of MySQL is also helpful. – Gordon Linoff Feb 07 '21 at 14:04

0 Answers0