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