Self Join Query Help

[Srikkanth Mohanasundaram]

Hello all,

I have a table like the one shown below
id parentChannelId
------------------------
item1 null
item2 item1
item3 item1
item4 item2
item5 item3
item6 item6
.....so on
..so on
I would want to get all the children (which includes the grandchildren) of item1 , I know i need to do a self join.But i am not sure on how to achieve this

   select  B.id  from 
 tableName as A join
 tableName as B on A.id=B.parentChannelId and A.id='item1' 

This would return item2,item3 How can i proceed further?

Thanks,
Srikkanth

[Jeanne Boyarsky]

See this recursive query thread.

[Srikkanth Mohanasundaram]

Thanks for the link Jeanne.

I read the thread and it means I should know the depth (number of levels). So assuming I've 3 levels now, i create a view with the parent-child mapping. In future if i need to increase the depth , i'll have to go back and update the view with one more join right ? Please correct me if am wrong.

[Jeanne Boyarsky]

Srikkanth Mohanasundaram wrote:I read the thread and it means I should know the depth (number of levels).

Or use a stored procedure.

[Scott Selikoff]

You can use a fixed depth but arbitrary length requires greater flexibility than SQL allows, such as stored procedures.

[Srikkanth Mohanasundaram]

Thanks Jeanne.Thanks Scott.

I think i would use a fixed length for my case.

Srikkanth