Is there a way to do an If Then statement in the join clause

Question

I have two tables, spend and bookings, that I'm trying to do a full join on. Due to the nature of the tables (not all bookings have spend, and not all spend has a booking_id, and sometimes the wrong booking_id gets assigned), I'd like to do a conditional statement in the join clause.

select bk.booking_id, bk.company_id, bk.event_id, bk.total_amount_booked, sp.spend
from 
(select booking_id, company_id, event_id, booked_rate, total_amount_booked
from bookings) bk
full join
(select booking_id, company_id, event_id, normalized_spend, spend
from spend) sp
on (bk.booking_id = sp.booking_id and bk.booked_rate = sp.normalized_spend)
or (bk.company_id = sp.company_id and bk.event_id = sp.event_id);

Basically, if the booking_id matches and the normalized spend matches the booked_rate, then join on that condition. If not, join on company_id and event_id.

However, doing this query returns

FULL JOIN is only supported with merge-joinable join conditions;

Is there a better way to do this? Using Redshift

Answer 1

This is a bit of pain, but you could emulate the full join with two left joins and union all:

select bk.booking_id, bk.company_id, bk.event_id, bk.total_amount_booked, sp.spend
from bookings bk
left join spend sp
    on (bk.booking_id = sp.booking_id and bk.booked_rate = sp.normalized_spend)
    or (bk.company_id = sp.company_id and bk.event_id = sp.event_id)
union all
select bk.booking_id, bk.company_id, bk.event_id, bk.total_amount_booked, sp.spend
from spend sp
left join bookings bk
    on (bk.booking_id = sp.booking_id and bk.booked_rate = sp.normalized_spend)
    or (bk.company_id = sp.company_id and bk.event_id = sp.event_id)
where bk.booking_id is null

Answer 2

Maybe using CASE statement, something like

...ON CASE WHEN (bk.booking_id = sp.booking_id and bk.booked_rate = sp.normalized_spend)
          THEN (bk.booking_id = sp.booking_id and bk.booked_rate = sp.normalized_spend)
         WHEN (bk.company_id = sp.company_id and bk.event_id = sp.event_id) 
          THEN (bk.company_id = sp.company_id and bk.event_id = sp.event_id)
    END