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

Reply via email to