1 Reply Latest reply: Apr 3, 2013 7:34 AM by Jim Smith RSS

    How to make recursive query in oracle

    Tarek Fathy
      Dear Experts:
      I have "Employees" table and "Departments" Table
      The structure of Dept Table is:
      ID   primary key
      parent_dept   foreign key(id)
      deptname  varchar2
      deptType varchar2
      
      The Employee table structure is
      ID primary key
      dept_id foreign key(Department.id)
      empName varchar2
      Sample data for departments
      ID : 1
      parent_dept : null
      deptname: General Manager office
      deptType : 'GM'
      
      ID :=2
      parent_dept : 1
      deptname: Information tech.
      deptType : 'DPT'
      
      ID :=3
      parent_dept : 2
      deptname: Software Development 
      deptType : 'SECTION'
      Sample Data for employees
      ID : 101
      dept_id  :1
      empName  King
      
      ID : 102
      dept_id  :2
      empName  ALAN
      
      ID : 103
      dept_id  :2
      empName  SAM
      
      ID : 104
      dept_id  :3
      empName  JANE
      I want to create a query that accepts a parameter "p_department_id" and returns All employees on the following conditions
      1- In case the parameter value is null , then retrieve All Employees "king - alan- sam-jane"
      2- In Case the parameter value is 1 , then retrieve all the employees under department id =1 in addition to all the employees located under the children departments.
      in this case it will be "king - alan- sam-jane"
      3- In case parameter value is 2 , then return all the employees under department id =2 in addition to all the employees located under the children departments.
      In this case it will be " alan- sam-jane"
      4- In case parameter value is 3 , then return all the employees under department id =3 in addition to all the employees located under the children departments.
      in this case it will be only "JANE"

      In brief , If I pass any value to the parameter :p_department_id , I want to retrieve all employees located in this department in addition to other employees located in the child's nodes of this department id

      Please help me

      Thanks