Show matching id data from table1 and others from table 1

byMR

May 29, 2022

I have these tables:

table1 (id, name, refernceid, salary, taxamount)
table2 (id, name, referenceid, salary,taxamount)

I want to show data from table2 if the reference id from table 2 exist in table1

table1

id, name, referenceid, salary,taxamount
1,  abs1, 12, 80k,1k
2,  abs2, 18, 90k,2k

table2

id, name, referenceid, salary,taxamount
1,  abs3, 12, 90k,2k
2,  abs4, 13, 90k,3k
3,  abs5, 14, 90k,4k

if refernce id matches in table 1 then display salary and name from table 1 otherwise show all data from table1

required output:

id, name, referenceid, salary,taxamount
1,  abs1, 12, 80k,2k
2,  abs4, 13, 90k,3k
3,  abs5, 14, 90k,4k

currently I have tried:

 select * from table1 t1 left join table2 t2 on t1.refernceid=t2.refernceid

but it is showing unmatched data with null i want two columns data from table 1 and others from table 2. Basically we are using table 1 as an updated table if the data is updated then we store reference id in table 1

>Solution :

It looks like you just need to use isnull:

select t2.id,
  isnull(t1.name, t2.name) name, 
  t2.referenceid, 
  isnull(t1.salary, t2.salary) salary
from table2 t2
left join table1 t1 on t1.referenceid = t2.referenceid;