Put this all together and you get: with org_chart ( To complete the query you need to union all the base and recursive queries together. These go between the query name and "as": with org_chart (Įmployee_id, first_name, last_name, manager_id When you use recursive with, you must provide aliases for all the columns it returns. This gives the following query: select e.employee_id, e.first_name, e.last_name, e.manager_id So you're linking the org_chart to the employees table. This is the employee from the previous row. Here you join the source table to the with clause on the columns storing the parent-child values.įor the company chart, you need to join each employee to their manager. So to begin the chart with the CEO, use: select employee_id, first_name, last_name, manager_id This is like the start with clause in connect by. You use this to define the root rows in your tree. This is the ANSI compliant way to build hierarchies in SQL. Oracle Database 11.2 introduced another method for accessing trees: recursive subquery factoring. Put this all together and you get the following query: select * from employeesĬonnect by prior employee_id = manager_id So you connect the prior employee_id to the current manager_id, like so: connect by prior employee_id = manager_id Thus you need to join the parent row's employee_id to the child's manager_id. In a company each employee's "parent" is their manager. You access values from the parent row using the keyword prior.
This links the columns that store the parent and child values. You state the parent-child relationship here. So you could identify them with: start with manager_id is null Connect By It's better to go with a more generic method. So you can begin the chart with him using: start with employee_id = 100īut if you do this, you need to change your query when a new CEO replaces him! Here that's employee_id 100, Steven King. These are the rows that appear at the "top" of the tree. It has two key clauses, start with and connect by. Connect by is an Oracle-specific way to create data trees using SQL.
0 kommentar(er)
