Re: Puzzle - HELP joining a table recursively++

Oracle has CONNECT BY construct to resolve queries of this kind. They call them hierarchical queries. But you will run into problem if the data is
in two tables, because hierarchical queries do not work with joins. The workaround I can see is to add a second column to the first table:

alter table add (parent number);

... populate it with relational data:

update setname set setname.parent = setcontents.id  where setcontents.member = setname.id;

And then enjoy hierarchical queries:

select rpad(' ',level*2,' ') || name from setname connect by prior id=parent start with parent is null;

The other way is to create a table based on join:

create table temp as select a.name, a.id, b.id parent from setname a, setcont b
where a.id = b.member(+);

and then run the same query against it.

Hope this helps!
Karen Abgarian.

petehorn_at_my-deja.com wrote:

> 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
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Fri Aug 20 1999 - 14:16:54 CDT