Hi,everyone!I have two tablesT1
s t
s1 t1
s2 t2
s3 t3T2
s x y
s1 x1 y1
s2 x1 y2
s1 x2 y3
s3 x2 y4And I want to get the following data by joining T1 and T2.t1 x1 y1
t2 x1 y2
t3 x1 0
t1 x2 y3
t2 x2 0
t3 x2 y4How can I get it?
s t
s1 t1
s2 t2
s3 t3T2
s x y
s1 x1 y1
s2 x1 y2
s1 x2 y3
s3 x2 y4And I want to get the following data by joining T1 and T2.t1 x1 y1
t2 x1 y2
t3 x1 0
t1 x2 y3
t2 x2 0
t3 x2 y4How can I get it?
(select t141.col2 col1,newtable1.col1 col2 from t141 full outer join (select distinct col2 col1 from t142 ) newtable1 on 1=1) newtable2
left outer join
(select t141.col1 col1,t141.col2 col2,t142.col2 col3,t142.col3 col4 from t141 full outer join t142 on t141.col1=t142.col1) newtable3
on newtable2.col1=newtable3.col2 and newtable2.col2=newtable3.col3
order by 1,2
(select t1.col1,t1.col2 ,t2.col2 col3 from t1 ,(select distinct col2 from t2) t2 ) t1,
t2
where t1.col3=t2.col2(+) and t1.col1=t2.col1(+)
order by t1.col3 ,col2