Reputation: 8646
If I have tlb1 as :
col1
1
2
3
Now I have tlb2 as:
col2 col3
4 Four
5 Five
6 SIX
No I have tlb3 as
col4 col5
sample14 sample15
sample24 sample25
sample34 sample35
What can be the query if I want result as :
col1 col2 col3 col4 col5
1 4 Four sample14 sample15
2 5 Five sample24 sample25
3 6 Six sample34 sample35
I tried with :
select ( (select * from tlb1), (select * from tlb2),(select * from tlb3)) T
But this failed.
Please help me.
Upvotes: 1
Views: 51
Reputation: 346
with t1 as (select col1, row_number() over (order by col1) rn from tbl1 ),
t2 as (select col2,col3, row_number() over (order by col2) rn from tbl2),
t3 as ( select col4,col5, row_number() over (order by col4) rn from tbl3)
select t1.col1,t2.col2,t2.col3,t3.col4,t3.col5
from t1 full outer join t2 on t1.rn = t2.rn
t3 full outerjoin t2 on t2.rn = t3.rn
try something like this...
Upvotes: 3