[SOLVED] Redshift: How to join with different key based on conditions

Redshift: How to join with different key based on conditions

I have two tables (t1, t2) to join based on id and year-month YYYY-MM

Generally, the join goes like this for example:

SELECT t1.id, t1.month, t2.price
FROM t1
JOIN t2
ON t1.id = t2.id AND t1.year_month = t2.year_month

Now, for one specific t1.id, say t1.id = 'a', I need to join with the previous month on t2 I.E.

SELECT t1.id, t1.month, t2.price
FROM t1
JOIN t2
ON t1.id = t2.id 
AND (t1.year_month = t2.year_month if t1.id != 'a' 
     else t1.year_month = t2.year_month - 1 month)

What I think to do is to create a temp table that year-month on t2 for id = 'a' is increased by one month. But t2 is a really large table so I don't really want to go with this route.

What is the best way to achieve this? Thanks!

Solution

Based on @JohnRotenstein, I tried to use a CASE statement in the JOIN clause and it worked.

Here's an example code for anyone who got here for the same question.

SELECT t1.id, t1.month, t2.price
FROM t1
JOIN t2
ON t1.id = t2.id 
AND t1.year_month = (
    CASE 
    WHEN t1.id = 'a' THEN t2.year_month - 1 month
    ELSE t2.year_month
    END
)