I have 2 tables:
table 1:
task cnt 1 4 2 5 3 6
table 2:
task cnt2 1 7 2 5 3 6 4 3
I want to add a column for table 2 such that if cnt in table1 for a task is the same as cnt2 for a task in table2. If there is no match, mark it as ‘no match’
desired result:
task cnt2 if_matched 1 7 'no match' 2 5 'yes' 3 6 'yes' 4 3 'no match'
I started from a query like the one below to pick tasks that have matched values
select task from table1 where table1.cnt = table2.cnt2
but I got an error for the where part.
Advertisement
Answer
Use left join and case expression to calculate matched:
select t2.task, t2.cnt, case when t1.task is null then 'no match' else 'yes' end as matched from table2 t2 left join table1 t1 on t1.task=t2.task and t1.cnt = t2.cnt2