Show matching id data from table1 and others from table 1-CodePudding

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

Desired output:

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

I have tried:

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

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

CodePudding user response：

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;