Search code examples
mysqlsqlprestotrino

SQL/Presto: how to choose rows if the values match with another table's

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.

Solution

  • 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