RE: SQL Question
Rajesh Kapur wrote
> I have a table where the hierarchy is built by storing the
> parent_id in the
> same table....
>
> id,
> parent_id
> .... other fields
>
> Can someone suggest an SQL statement that will list the
> entire hierarchy as
> follows
>
> id1
> id1.1
> id1.2
> id1.2.1
> id1.2.2
> id1.2.3
> id1.2.3.1
> id1.3
> id1.4
> id2
> ....
At the risk of making myself look silly...
This is the common Bill of Materials, or Foreign Key constraint problem.
I am certain it can only be done with temp tables or perhaps Stored
Procedures.
The way I have tackled this in the past is ...
select from tableX id, parent_id , 0 rank_no into temp ranking ;
update ranking set rank_no = 1 where parent_id is null or = "" ;
select id from ranking where rank_no = 0 and parent_id in (
select id from ranking where rank_no <> 0 )
into temp temp2 ;
update ranking set rank_no = 2 where id in ( select id from temp2 ) ;
select id from ranking where rank_no = 0 and parent_id in (
select id from ranking where rank_no <> 0 )
into temp temp3 ;
update ranking set rank_no = 3 where id in ( select id from temp3 ) ;
.
.
.
and repeat for the maximum number of hierarchies .....
You now have a table with the level of each ID in so...
select spaces ( b.rank_no * 4 ), a.id from tableX a, ranking b
where a.id = b.id
order by rank_no, a.id
( doubt if this syntax is correct as I have not tested it )
Colin Bull
c.bull@videonetworks.com
sending to informix-list