tsql - How to create this Hierarchy -

i have 3 tables: structures, account range , accountvalueusd. hierarchy of parent , child id in table structure, want create hierarchy :

level 1...level 2...level 3...level 4....account...valueusd 111       112   113     114     100     1000 111     112     113     114     101     2000 

the table structure links table account range key: financialitem

the table acountrange links table account value key: accountfrom , accountto accountnumber

can please me how it?

    create table [dbo].[structure](     [financialitem] [nvarchar](3) null,     [id] [int] null,     [parentid] [int] null,     [childid] [int] null,     [nextid] [int] null,     [level] [int] null ) on [primary] insert [dbo].[structure] values (111,1,null,2,null,1), (112,2,1,3,null,2), (113,3,2,4,null,3), (114,4,3,null,null,4), (221,5,2,6,null,3), (222,6,5,null,7,4), (223,7,5,null,null,4)      create table [dbo].[accountrange](     [financialitem] [nvarchar](3) null,     [accountfrom] [int] null,     [accountto] [int] null ) on [primary] insert [dbo].[accountrange] values (114,100,105), (222,200,205), (223,300,305)       create table [dbo].[accountvalue](     [accountnumber] [int] null,     [valuesusd] [int]  null, ) on [primary] insert [dbo].[accountvalue] values (100,1000), (101,2000), (301,1500), (201,1400) 

using data provided, query gives output specified. it's hard coded 4 levels, if needed more dynamic, further thought required.

i've assumed reason 221 branch didn't appear because needs match on both parent , child id columns.

select l1.financialitem [level 1]      , l2.financialitem [level 2]      , l3.financialitem [level 3]      , ar.financialitem [level 4]      , av.accountnumber, av.valuesusd accountrange ar inner join accountvalue av on av.accountnumber between ar.accountfrom , ar.accountto inner join structure l4 on l4.financialitem = ar.financialitem inner join structure l3 on l3.id = l4.parentid , l3.childid = l4.id inner join structure l2 on l2.id = l3.parentid , l2.childid = l3.id inner join structure l1 on l1.id = l2.parentid , l1.childid = l2.id