@ Greg

thanks man, this is what I was exactly searching for.. "expanding hierarchies". Thanks you very much, I guess my problem is solved.

@ Lynn Pettis

thanks for helping out, I'll come...

what I expect is to write a query which returns all the leaf nodes, for example

with reference to above tree..

If I query the leaf nodes of 'A' it should...

@ramesh-2 thanks a lot, that was very informative.

@lynn pettis

I've created a table as

create database bom

use bom

create table bom (item_id int, item_name varchar(15), qty int, parent varchar(10),parent_id int, has_child int, primary...