-1

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

Parfait
  • 104,375
  • 17
  • 94
  • 125
meliwazhere
  • 13
  • 2
  • 1
    Aside - RedShift does not run on PostgreSQL. It is a distinct DBMS that originated with an earlier Postgres dialect. – Parfait Oct 16 '20 at 22:01
  • DataGrip says I'm running PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.19097. I'm assuming that it's an older version of Postgres, but there are some functions you can't do on Redshift that you can do on PostgreSQL. – meliwazhere Oct 16 '20 at 22:05

2 Answers2

0

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
GMB
  • 216,147
  • 25
  • 84
  • 135
0

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
z44.nelther
  • 898
  • 5
  • 8