HUE SQL where clause value in field from one table like value in field in another table

Question

In my where clause I want to search for a value in field in another table

eg. I have a field in table called channel in can contain values 'online', 'instore' or 'both' and have a order table that contains only 'online' or 'instore' I tried to change the value 'both' in the first table to 'onlineinstore'

case when channel = 'both' then 'onlineinstore' else channel as channel

when I join table 1 to the orders table for an order is online nothing returns, but if the channel is either online or instore I want is to match with onlineinstore unless the channel in table 1 is online or instore

select * from t1 join order
on t1.orderid = order.orderid
where order.channel like t1.channel

Answer 1

use case expression in the join condition:

select * from t1 
  join order as t2
    on t1.orderid = t2.orderid
   and (
         (t1.channel=t2.channel) or (t1.channel='both' and t2.channel in('online', 'instore'))
       )

If non-equijoins do not work in your version than move them to the where clause

select * from t1 
  join order as t2
    on t1.orderid = t2.orderid
  where (
          (t1.channel=t2.channel) or (t1.channel='both' and t2.channel in('online', 'instore'))
        )