db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Getting transitive closure?
Date Sun, 14 Oct 2012 11:52:44 GMT
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):

   insert into COMPONENTS(COMPONENT,PARENT) values
      ('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?

TIA,
-- 
John English

Mime
View raw message