Re: Parent and children

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 23 Sep 1999 10:22:57 +0200
Message-ID: <7sco2e$el6$1@oceanite.cybercable.fr>

Sorry i've lost the original thread.

It was about two tables a parent and a child one. Each parent may have up to 3 children and the author wanted a row per parent with its children ordering by one of its column.

Here's a solution:

v734>create table parent (id number primary key);

Table created.

v734>create table child (id number primary key,

  2                      parent_id number references parent);

Table created.

v734>select * from parent order by id;

        ID

---

         0
         1
         2
         3
         4

5 rows selected.

v734>select * from child order by parent_id, id;

        ID PARENT_ID
---------- ----------

        11          1
        21          2
        22          2
        31          3
        32          3
        33          3
        41          4
        42          4
        43          4
        44          4

10 rows selected.

v734>select p.id id,

  2         max(decode(c.nb, 1, c.id, '')) child1,
  3         max(decode(c.nb, 2, c.id, '')) child2,
  4         max(decode(c.nb, 3, c.id, '')) child3
  5  from parent p,
  6       (select a.parent_id, a.id, count(*) nb from child b, child a
  7        where b.parent_id = a.parent_id and b.id <= a.id
  8        group by a.parent_id, a.id) c

  9 where c.parent_id (+) = p.id
 10 group by p.id;

        ID CHILD1 CHILD2 CHILD3
---------- ---------- ---------- ----------

         0
         1         11
         2         21         22
         3         31         32         33
         4         41         42         43

5 rows selected. Received on Thu Sep 23 1999 - 03:22:57 CDT