db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From José Ventura <st.ne...@gmail.com>
Subject Re: Getting transitive closure?
Date Sun, 14 Oct 2012 12:45:23 GMT
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:

http://old.nabble.com/Recursive-query-on-common-table-td25819772.html


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):
>
>   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