Here's one way to do it by a selfjoin.
There's a drawback, though, in that using a function in the join will invalidate indexusage,
so there may not be the best performance on large volumes.
create
table #x ( ord_num int not null, parent_num int not null, ord_txt varchar(15) not null )
insert
#x
select 848313, 0, 'order 848313' union all
select 848314, 848313, 'order 848314' union all
select 848315, 0848314, 'order 848315'
select b.ord_num, b.parent_num, b.ord_txt
from #x a
join #x b
on coalesce(nullif(b.parent_num, 0), b.ord_num) = a.ord_num
ord_num parent_num ord_txt
----------- ----------- ---------------
848313 0 order 848313
848314 848313 order 848314
848315 848314 order 848315
(
3 row(s) affected)
/Kenneth