#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2018
    Posts
    2
    Rep Power
    0

    JOIN on matching column and date2 < date 1


    I have two tables, Action1 and Action2.

    I need to be able to return all of the rows from Action1, and only most recent corresponding date2 column from the Action2 table where the value in Action2.date2 is less than the value in Action1.date1 and Action1.code = Action2.code. There are multiple records for each "code" in both tables.

    If table Action2 does not have a date2 that meets this criteria, or has no corresponding row, a null should be included in the result set.

    I can achieve this by using a cursor to loop through each row in the Action1 table and then performing a query against the Action2 table, but I am hoping to find a more streamlined solution.

    Thank you in advance for your help.

    Example Data


    Action1
    code , date1
    123, 2018-01-01
    123, 2018-01-05
    123, 2018-01-15
    234, 2018-01-04
    234, 2018-01-07
    234, 2018-01-20
    345, 2018-02-01

    Action2
    code, date2
    123, 2017-12-31
    123, 2018-01-01
    123, 2018-01-03
    234, 2017-12-31
    345, 2018-02-02

    Expected Output
    action1.code, action1.date1, action2.date2
    123, 2018-01-01, 2017-12-31
    123, 2018-01-05, 2018-01-03
    123, 2018-01-15, 2018-01-03
    234, 2018-01-04, 2017-12-31
    234, 2018-01-07, 2017-12-31
    234, 2018-01-20, 2017-12-31
    345, 2018-02-01, null
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,792
    Rep Power
    4331
    Code:
    SELECT action1.code
         , action1.date1
         , MAX(action2.date2) AS date2
      FROM action1
    LEFT OUTER
      JOIN action2
        ON action2.code = action1.code
       AND action2.date2 < action1.date1 
    GROUP
        BY action1.code
         , action1.date1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2018
    Posts
    2
    Rep Power
    0
    Thank you r937. I was overthinking it this whole time. Works great.

IMN logo majestic logo threadwatch logo seochat tools logo