Query required

Question

Hi, I have two tables - Table1, Table2 . We can join these tables based on ID. In table1will have latest data and if it is having pop values then i have to get those values else i have to get cid values from table2.

create table1 (id varchar(10), popcid1 int,popcid2 int,popcid3 int)
create table2 (id varchar(10), cid1 int,cid2 int,cid3 int)

insert into table1(id,popcid1,popcid2,popcid3)
select 1,3,4,6
union
select 2,3,4,7
union
select 3,3,4,8

insert into table2(id,cid1,cid2,cid3)
select 3,3,4,5
union
select 4,13,14,15
union
select 5,11,14,15

output

id, popcid1,popcid2,popcid3
1,3,4,6
2,3,4,7
3,3,4,8
4,13,14,15
5,11,14,15

Answer 1

Please try:

select * from table1  
union   
select * from table2  
where id not in(select id from table1)  

Or:

select * from table1  
union   
select * from table2  
where id not in(select id from table1  
                where popcid1 is not null or  
 popcid2 is not null or  
 popcid3 is not null)  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

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

Answer 2

It seems you want to insert data to table1 if id does not exist in the table1 table. You can use the MERGE statement:

MERGE table1 AS t  
USING table2 AS s  
	ON t.id = s.id  
WHEN NOT MATCHED THEN  
	INSERT (  
		popcid1,  
		popcid2,  
		popcid3  
	)  
	VALUES (  
		s.cid1,  
		s.cid2,  
		s.cid3  
	);