select
t3.name1,t3.name2,t4.
name
from
(
select
t1.code code1,t1.
name
name1,t1.parentcode parentcode1,t2.code code2,t2.
name
name2,t2.parentcode parentcode2
from
t t1
left
join
t t2
on
t1.code=t2.parentcode
where
t1.parentcode=0) t3
left
join
t t4
on
t3.code2=t4.parentcode