CODEWITHSUNDEEP
sqlsql-server-2008-r2
C Sharper
C Sharper

Reputation: 8646

Combine multiple tables in one

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

Answers (1)

CMadhu
CMadhu

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

Related Questions