John, I suggest that you take a look at Warshall's Algorithm. See, for example, http://en.wikipedia.org/wiki/Floyd%E2%80%93Warshall_algorithm. Google Warshall's Algorithm for additional links.
_________________________________________ John I. Moore, Jr. SoftMoore Consulting email: [email protected] web: www.softmoore.com cell: 843-906-7887 > -----Original Message----- > From: John English [mailto:[email protected]] > Sent: Sunday, October 14, 2012 7:53 AM > To: Derby Discussion > Subject: Getting transitive closure? > > 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
