> For the complete documentation index, see [llms.txt](https://www.pranaypourkar.co.in/the-programmers-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.pranaypourkar.co.in/the-programmers-guide/database/sql-databases/resources-and-references/oreilly-sql-cookbook-2nd-edition/13.-hierarchical-queries.md).
# 13. Hierarchical Queries
## Expressing a Parent-Child Relationship
We want to include parent information along with data from child records. For example, we want to display each employee’s name along with the name of their manager.
Sample Output
```
-- Self join the table
select a.ename || ' works for ' || b.ename as emps_and_mgrs
from emp a, emp b
where a.mgr = b.empno
```
## Expressing a Child-Parent-Grandparent Relationship
We want to find Manager of a Manager of an employee. For example, Employee CLARK works for KING, and CLARK was in turn a manager for another employee SHARK. (Three-Tier Relationship)
SHARK -> CLARK -> KING
```
select ltrim(
sys_connect_by_path(ename,'-->'),
'-->') leaf___branch___root
from emp
where level = 3
start with ename = 'MILLER'
connect by prior mgr = empno
```
## Creating a Hierarchical View of a Table
In the case of the EMP table, employee KING has no manager, so KING is the root node. You want to display, starting from KING, all employees under KING and all employees (if any) under KING’s subordinates.
Sample Output
```
select ltrim(
sys_connect_by_path(ename,' - '),
' - ') emp_tree
from emp
start with mgr is null
connect by prior empno=mgr
order by 1
```
## Finding All Child Rows for a Given Parent Row
We want to find all the employees who work for JONES, either directly or indirectly.
Sample Output
```
ENAME
----------
JONES
SCOTT
ADAMS
FORD
SMITH
```
```
-- Oracle
select ename
from emp
start with ename = 'JONES'
connect by prior empno = mgr
```
## Determining Which Rows Are Leaf, Branch, or Root Nodes
We want to determine what type of node a given row is: a leaf, branch, or root.
**Sample Output**
```
select
ename,
connect_by_isleaf is_leaf,
(select count(*) from emp e
where e.mgr = emp.empno
and emp.mgr is not null
and rownum = 1) is_branch,
decode(ename,connect_by_root(ename),1,0) is_root
from emp
start with mgr is null
connect by prior empno = mgr
order by 4 desc, 3 desc
```
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:
```
GET https://www.pranaypourkar.co.in/the-programmers-guide/database/sql-databases/resources-and-references/oreilly-sql-cookbook-2nd-edition/13.-hierarchical-queries.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.