0

From a table with column structure (parent, child) I need:

  1. For a particular parent I need all children.
  2. From the result of (1) I need the children's children too.

For example for parent=1:

    parent|child  parent|child   parent|child
    1      a        a     d        b      f
           b              e               g
J.P.
  • 350
  • 2
  • 11
MAN
  • 1
  • 2
    Do you want 3 separate recordset or all result in one recordset? – Ali Sheikhpour Nov 27 '18 at 21:48
  • 2
    Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using `postgresql`, `oracle`, `db2`, `sql-server`, ... –  Nov 27 '18 at 21:55
  • Can you post the parent|child input data which would yield your example result? I'm having a bit of trouble understanding what you are trying to do. – Error_2646 Nov 27 '18 at 22:27
  • I am using DB2 and i need all results in one recordset – MAN Nov 29 '18 at 15:03

1 Answers1

0

This gets you the information you say you want, I think. Two columns: child and grandchild (if any, or else NULL). Not sure if it's the schema you'd like, since you don't specify. You may add JOINs to increase the recursion depth.

select t1.child, t2.child
from T as t1 left join T as t2
on t1.child = t2.parent
where t1.parent = 1

This works on SQLite; I think it's quite standard. Regarding schema if this one doesn't serve you, hopefully it may give you ideas; or else please specify more.

J.P.
  • 350
  • 2
  • 11
  • This is not accurate when the relationship has more than 3 levels, for example A has child B, and B has child C, The question actually is a common scenario that can be solved using recursion. – jyao Dec 06 '18 at 16:37