I might be mistaken, but I think Derby does not support this out-of-the-box. There are, however, a few interesting solutions suggested in this (2009) thread:


On Sun, Oct 14, 2012 at 8:52 AM, John English <john.foreign@gmail.com> wrote:
If I have a table defined like so:

  create table COMPONENTS (
    ID          integer       generated always as identity,
    COMPONENT   varchar(255)  not null,
    PARENT      integer,
    constraint COMPONENTS_PK  primary key (ID),
    constraint COMPONENTS_1   foreign key (PARENT)
                              references COMPONENTS(ID)
                              on delete cascade

with (for example):

     ('IC package',null),    -- ID = 1
     ('Flip-flop',1),        -- ID = 2
     ('D-type',2),           -- ID = 3
     ('JK flip-flop',2),     -- ID = 4
     ('7474 dual D-type flip-flop',3),
     ('4013 dual CMOS D-type flip-flop',3),
     ('7476 dual JK flip-flop with preset and clear',4),
     ('4027 dual CMOS JK flip-flop',4);

Is there a clever an efficient way to get the set of all flip-flop packages, i.e. ID=2, all items with parent=2, and so on down the chain of descendants? Or in other words, the transitive closure of the set?

John English