Re: hierarchical sql queries

On Sun, 26 Apr 1998 17:52:41 +0200, guerillot g <gg200492_at_club-internet.fr> wrote:

>my question is about hierarchical sql queries:
>
>this is my table structure : str_parent
> str_child
>
>
>datas 1
>
> 2 3 4
>
> 5 6 7 8 9 10 11 12
>
> 13 14 15 16 17 18 19 20 21 22
>
>
>i 'd like to write sql query to obtain the following result:
>
>column_name level2 level4
> row1 2 13
> row2 2 14
> row3 2 15
> 2 16
> 2 17
> 2 18
> 3 19
> 3 20
> 3 21
> 4 22

You have your data structured in a hierarchical manner, but I don't think you can use the hierarchical facilities provided by Oracle in the SELECT statement - the START WITH and CONNECT BY clauses, since they will return a hierarchical path, given a start point. I simulated your problem in a temporary table and tried to use these clauses, but no results, since I was unable to extract only the 2nd and 4th levels.

The following select will solve your problem, though:

select a.str_parent level2, b.str_child level4 from teste a, teste b
where a.str_child=b.str_parent
and a.str_parent <> 1
order by a.str_parent;

Good luck,

Nuno Guerreiro Received on Mon Apr 27 1998 - 08:38:36 CDT