Re: Puzzle - HELP joining a table recursively++

From: fump <fumi_at_tpts5.seed.net.tw>
Date: 22 Aug 1999 10:00:35 GMT
Message-ID: <7poho3$t95$3@news.seed.net.tw>

<petehorn_at_my-deja.com> wrote in message news:7pk50r$4p2$1_at_nnrp1.deja.com...
> Hello all -
>
> I have a SQL puzzle.
> I've simplified my situation somewhat but it still retains the same
> challenge. Perhaps this is simple -- please educate me.
>
> The first table just contains a set ID and name. As an example, I'll
> use databases.
>
> Table 1 - setname
> id name
> ---- ------------
> 1 oracle
> 2 sybase
> 3 db2
> 4 ims
> 5 relational databases
> 6 databases
>
> Table 2 - setcontents
>
> id member
> -- ----------
> 5 1
> 5 2
> 5 3
> 6 4
> 6 5
>
> Table 2 contains the relationship among the values in Table 1 so as to
> form hierarchical sets of information. In other words the set of all
> databases (id=6) consists of the set of relational databases (id=5) and
> ims (id=4); and the set of relational databases (id=5) consists of
> oracle, sybase, and db2.
>
> What's the SQL to expand all the sets recursively?
>
> Thanks.
> pete

Though Oracle's hierarachial can query only one table, it's still possible to do.

You can use it as a in-line view and join it with other tables. One point to notice is that Oracle may change the table order in join and return resultset in a different order, so it's necessary to reserve the row order in the hierarachial in-line view.

select lpad(' ', 2*lvl-2)||a.name as item, b.name from (

    select id, member, level as lvl, rownum as r

      from setcontents
      connect by prior member=id) c,

    setname a, setname b
where a.id=c.id and b.id=c.member
order by c.r; Received on Sun Aug 22 1999 - 05:00:35 CDT