CODEWITHSUNDEEP
sqloracle-databasehierarchical-datarecursive-query
Ronald
Ronald

Reputation: 2917

SQL how to get parent and its all children and children of children... etc

I have to see I am new to sql.

I have a table which looks like this:

child      parent
-------------------
Nancy      Bob
Oli        Bob
Alice      Oli
Mira      Alice

Now I need sql statement which gives me parent -which is already known- and its children and children of its children... etc, given is parent name.

Example I need Family of Bob. The sql should return (Bob, Nancy, Oli, Alice, Mira)

I tried this: select child from myTable where parent='Bob' This gives me Nancy and Oli.

Any Idea?

Upvotes: 0

Views: 1431

Answers (2)

Atif
Atif

Reputation: 2210

You can use Oracle's hierarchial queries to get this along with the level of tree:

Demo

SELECT CHILD,  LEVEL FROM TABLE1
START WITH PARENT = 'Bob'
connect by prior child = parent;

Upvotes: 1

GMB
GMB

Reputation: 222652

That’s a typical hierarchical query. One standard method uses a recursive with clause:

with cte (child, parent) as (
    select child, parent from mytable where parent = 'Bob'
    union all
    select t.child, t.parent
    from mytable t
    inner join cte c on c.child = t.parent
)
select child from cte

Upvotes: 0

Related Questions