question

leodec-4354 avatar image
0 Votes"
leodec-4354 asked EchoLiu-msft commented

merging multiple tables

How to merge 3 tables data with a common column.

 create table #t1 (id varchar(3), name_1 varchar(10))
 insert into #t1 values (101,'red')
 insert into #t1 values (102,'green')
 insert into #t1 values (103,'blue')
    
 create table #t2 (id varchar(3), address_1 varchar(10), state_1 varchar(10))
 insert into #t2 values (101,'lon','ab')
 insert into #t2 values (101,'fin','pq')
 insert into #t2 values (102,'san','cd')
 insert into #t2 values (102,'new', 'mn')
    
 create table #t3 (id varchar(3), examdt varchar(10), examtime varchar(10), centre_code varchar(10))
 insert into #t3 values (101,'2017-01-01','08:05', 'cod')
 insert into #t3 values (101,'2018-04-15','07:15','salm')
 insert into #t3 values (101,'2015-08-08', '12:25','targ')
 insert into #t3 values (102,'2019-03-11','09:45', 'hen')
 insert into #t3 values (102,'2020-05-25','17:15','mint')
 insert into #t3 values (103,'2015-05-08', '12:45','fish')
 I tried:
    
 select u.id, name_1, address_1, state_1, examdt, examtime, centre_code from 
 (
 select id from #t1 union
 select id from #t2 union
 select id from #t3 ) as u left outer join #t1 on #t1.id = u.id
 left outer join #t2 on #t2.id = u.id
 left outer join #t3 on #t3.id = u.id 

After running the above script, it is doing a cross join.

I want it should bring 3 rows with all non-common columns.

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you have any update?Did the following methods solve your problem?If not, please provide more details.If it is resolved,please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

I want it should bring 3 rows with all non-common columns

And exactly how would that output look like? In #t1 there is one row per id, but there are two rows with id = 101 in #t2 and three more in #t3. Likewise there are multiple rows of 102. If you only want three rows, you will need give some thought which rows to pick.

So, yes, you get a row multiplication when you join them that way, but without knowing what result you want, I can't give you a query.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @leodec-4354

Is the following what you want?

 ;with cte
 as(select *,row_number() over(partition by id order by id) rr FROM #t1)
 ,cte2 
 as(select *,row_number() over(partition by id order by id) rr FROM #t2)
 ,cte3 
 as(select *,row_number() over(partition by id order by id) rr FROM #t3)
    
 select c.id, name_1, address_1, state_1, examdt, examtime, centre_code 
 from cte c
 left join cte2 c2 on c.id = c2.id and c.rr = c2.rr
 join cte3 c3 on c.id =c3.id and c.rr = c3.rr

Output:
99311-image.png

If this does not solve your problem, please also provide your desired output and more details.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.



image.png (7.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.