递归又来了！思路明确

有表tb1:(ITEM_CODE指成品)
SQL code

ITEM_CODE
99-88551-01030
98-78496-SP417K
......

有表BOM1：
其中PARENT与ITEM_CODE可以存在不定的多层的递归关系，层层递归，99下面是98，98下是97,97下是94,94下是91，91下面是20或者21,22等等。例如：
SQL code

PARENT(成品)            ITEM_CODE(半成品或物料)        BOM_UNIT
99-88551-01030           97-88551-01030                   PCS  ----此为最高层
97-88551-01030           96-88551-01010                   PCS
96-88551-01010           #8003042-01010                   G
96-88551-01010           #6300035-01010                   G
96-88551-01010           20-88551-01030                   PCS
20-88551-01030           21-00001-00010                   G
20-88551-01030           22-00424-01010                   G

98485-00001              97485-00001                      PCS  ----此为最高层
97485-00001              90485-00001                      PCS
90485-00001              #8000-01010                      G
90485-00001              20485-00001                      PCS
20485-00001              21485-00001                      PCS
20485-00001              22485-00001                      G

98-78496-SP417K          #98-78496-SP417K                 G       ----此为最高层
98-78496-SP417K          97-78496-SP417K                  PCS
97-78496-SP417K          96-78496-SP2027                  PCS
97-78496-SP417K          #7678496-00010                   G
97-78496-SP417K          #88-78496-SP2027                 G
96-78496-SP2027          94-78496-SP2027                  PCS
96-78496-SP2027          #87-78496-SP2027                 G
94-78496-SP2027          90-78496-01010                   PCS
90-78496-01010           87-02029-00020                   G
90-78496-01010           02-00002-01011                   G

我想要的结果：想通过表tb1一次性查找它底层的所有物料，半成品，成品。不管有没有重复的，都将结果写进一个表：
SQL code

99-88551-01030           --99-88551-01030 下面的所有物料
97-88551-01030                   
97-88551-01030           
96-88551-01010                   
96-88551-01010           
#8003042-01010                  
96-88551-01010           
#6300035-01010                   
96-88551-01010           
20-88551-01030                   
20-88551-01030           
21-00001-00010                   
20-88551-01030
22-00424-01010                   
20-88551-01030

98-78496-SP417K          --98-78496-SP417K 下面的所有物料
#98-78496-SP417K                 
98-78496-SP417K          
97-78496-SP417K                  
97-78496-SP417K          
96-78496-SP2027                  
97-78496-SP417K          
#7678496-00010                   
97-78496-SP417K          
#88-78496-SP2027                 
96-78496-SP2027          
94-78496-SP2027                  
96-78496-SP2027          
#87-78496-SP2027                 
94-78496-SP2027          
90-78496-01010                   
90-78496-01010           
87-02029-00020                   
90-78496-01010           
02-00002-01011                   

数据库环境： SQL 2000
参考类似贴：http://topic.csdn.net/u/20111111/15/faeb68e1-195c-4492-8371-0465c4b1beed.html

BOM1测试数据：
SQL code

create table BOM1(PARENT varchar(50),ITEM_CODE varchar(50),BOM_UNIT varchar(50))

insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('99-88551-01030','97-88551-01030','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-88551-01030','96-88551-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','#8003042-01010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','#6300035-01010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','20-88551-01030','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20-88551-01030','21-00001-00010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20-88551-01030','22-00424-01010','G')

insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98-78496-SP417K','#98-78496-SP417K','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98-78496-SP417K','97-78496-SP417K','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','96-78496-SP2027','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','#7678496-00010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','#88-78496-SP2027','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-78496-SP2027','94-78496-SP2027','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-78496-SP2027','#87-78496-SP2027','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('94-78496-SP2027','90-78496-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90-78496-01010','87-02029-00020','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90-78496-01010','02-00002-01011','G')



insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98485-00001','97485-00001','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97485-00001','90485-00001','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90485-00001','#8000-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90485-00001','20485-00001','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20485-00001','21485-00001','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20485-00001','22485-00001','G')

等晴天大大或者大版算了 好像这个一直是他们在做。