how create select query to get all the child node of any particular node in tree structure db table

Question

My Tree Structure is

and the database table i have created is

how create select query to get all the child node of any particular node in tree structure db table.

for Example i pass superior_emp_id=1 then it return {2, 3, 4, 5, 6, 7}

Answer 1

select * 
from employee 
where superior_emp_id = @emp_id  

union all  

select * 
from employee 
where superior_emp_id in (select emp_id 
                          from employee 
                          where superior_emp_id = @emp_id)

Answer 2

Well you can make use of nested queries and sets here,

You can try the following query for getting the child nodes of root node:

select *
from TABLE_NAME
where senior_emp_id in { select emp_id
                         from TABLE_NAME
                         where senior_emp_id = 1 || emp_id = 1 }

Similarly if you put 2 in place of 1 in the nested query if you want to have child nodes of Parent node -2

Let me know if it's working well ..

Answer 3

StringBuilder sql = new StringBuilder();
        sql.append("SELECT emp_id AS id, ");
        sql.append("  emp_name AS employeeName, ");
        sql.append("  title AS title, ");
        sql.append("  superior_emp_id AS parentId ");
        sql.append(" FROM T_NWM_SRVC ");
        sql.append(" WHERE superior_emp_id IS NOT NULL ");
        sql.append(" AND superior_emp_id  =");
        sql.append(parentId);

On passing parentId : 1 you will retrieve 2,3.For getting childnodes of 2,3 you will have to do a server call again(Kind of lazy loading). Hope this is what you are looking for.

Answer 4

I think that you cant do this with one sql Statement. I would try to implement a recursive function that querys for a set of given ids if there more childs and append them. Prosa:

function List<Integer> getChilds(int[] ids)
{  
List<Integer> returnValue = new ArrayList();
if(ids.length = 0)
    return returnValue;

foreach int id : ids
{
    //Build sql to get childs an execute it
    int[] childidsfromsql = em.createQuery("...").getResultList();..
    returnValue.addAll(getChilds(childidsfromsql))
}
 return returnValue;
}