SQL Query - join on less than or equal date

Question

I'm having trouble figuring out how to format the following query. I want to take Table A and left join Table B. Not only on a.country = b.country but also on the max start_date that is less than equal to join_date.

Postgres (Redshift)

Table A

  ID      join_date     country          email     
 -----    -----------   ---------        ------
  124     '2013-10-03'     US         [email protected]
  423     '2013-04-21'     CA         [email protected]
  412     '2013-03-30'     US         [email protected]

Table B

  start_date        country          joined_from
-------------     -----------       --------------
'2013-08-21'          US                google
'2014-01-02'          CA                yahoo
'2013-03-02'          CA                microsoft
'2013-02-10'          US                facebook
'2013-09-01'          US                yahoo

End Result

  ID     join_date     country      email         start_date     joined_from
------  -----------    ---------   ---------     ------------   -------------
 124    '2013-10-03'     US        [email protected]   '2013-09-01'     yahoo
 423    '2013-04-21'     CA        [email protected]    '2013-03-02'    microsoft
 412    '2013-03-30'     US        [email protected]  '2013-02-10'    facebook

Answer 1

Not pretty, but putting your condition directly in the JOIN should work:

SELECT a.ID, a.join_date, a.country, a.email, b.start_date, b.joined_from
FROM a LEFT JOIN b ON a.country = b.country 
      AND b.start_date = (
          SELECT MAX(start_date) FROM b b2 
          WHERE b2.country = a.country AND b2.start_date <= a.join_date
      );