SQL Parent Row 찾기!

간단한 쿼리문..
1시간동안 해맸던.

---------------------------
간단한 테스트 테이블
---------------------------
create table Test
(
   child varchar(100),
   parent varchar(100),
   data varchar(100)
)

------------------------------------
테스트 데이터 : child 컬럼의 데이터는 null(최상위) 이거나 부모를 가지고 있다
------------------------------------

insert into Test values('1', null,'data1')
insert into Test values('2', '1','data2')
insert into Test values('3', '1','data3')
insert into Test values('4', '2','data4')
insert into Test values('5', '1','data5')
insert into Test values('6', '1','data6')
insert into Test values('7', '3','data7')
insert into Test values('8', '3','data8')
insert into Test values('9', '1','data9')
------------------------------------
셀프 조인을 통해(left join) 부모데이터와
자식 데이터를 나란히 위치 시킨다
------------------------------------
select t1.child , t1.parent, t1.data childData, t2.data parentData
from Test t1 left outer join
Test t2 on t1.parent = t2.child
------------------------------------
원래 결과
child   parent        data
1           NULL      data1
2           1           data2
3           1           data3
4           2           data4
5           1           data5
6           1           data6
7           3           data7
8           3           data8
9           1           data9

쿼리후 나온 결과
child    parent childData      parentData
1           NULL      data1           NULL
2           1           data2           data1
3           1           data3           data1
4           2           data4           data2
5           1           data5           data1
6           1           data6           data1
7           3           data7           data3
8           3           data8           data3
9           1           data9           data1


끝