easy family tree query

Question

There is only one table.

Table name Family_tree.

Query all children for Nick. Nick can be also mother!

NAME                 ID    FATHER_ID    MOTHER_ID
--------------- ---------- ---------- ----------
Nick                  23         25        24
Jane                  10         27        26
Perl                  15         9         13
Katrin                50         6         12
Sandra                1          3          8
Demi                  2          3          8
Deimar                3          7          5
Gandalf               4          6          5
Bill                  5         10         23
Kelly                 6         22         43
Dolmar                7         11         20

Answer 1

May be like this:

select name, father_id, mother_id from family_tree 
  where father_id in 
(select id from family_tree
  where name = 'Nick')
or mother_id in 
 (select id from family_tree
  where name = 'Nick')

or if you need Nick only as a FATHER, then:

select name, father_id, mother_id from family_tree 
      where father_id in 
    (select id from family_tree
      where name = 'Nick')

Also, if you need only children , you don't need to use connect by...prior for grandchildren, grandgrandchildren, etc.