SQL column of conditional JOIN
I want to define a JOIN column based on the value of the current row.
So, for example, my job table has 4 date columns: offer_date, accepted_date, start_date, report_date.
I want to check the date based exchange rate. I know report_date is never null, but this is my last resort, so I have a priority order for joining with the exchange_rate table. I'm not really sure how to do it with a CASE statement, if that's even the correct approach.
INNER JOIN exchange_rate c1 ON c1.date = {{conditionally pick a column here}}
-- use j.offer_date if not null
-- use j.accepted_date if above is null
-- use j.start_date if above two are null
-- use j.reported_date if above three are null
+3
Adam Levitt
source
to share
2 answers
Try the logic like this:
INNER JOIN
exchange_rate c1
ON c1.date = coalesce(j.offer_date, j.accepted_date, j.start_date, j.reported_date)
The function coalesce()
returns the first non-NULL value in the list.
+9
Gordon linoff
source
to share
A CASE statement might look something like this:
INNER JOIN exchange_rate c1 ON c1.date =
CASE
WHEN j.offer_date IS NOT NULL THEN j.offer_date
WHEN j.accepted_date IS NOT NULL THEN j.accepted_date
WHEN j.start_date IS NOT NULL THEN j.start_date
ELSE j.reported_date
END
+4
olzerowicz
source
to share