join two tables with date interval 3 days before and 3 days after

Question

I'm trying to join two tables below

table 1

ID    | submit_checkdate
----- | -----------
1     | 2017-07-31
2     | 2017-07-31
3     | 2017-07-31
4     | 2017-07-15

table 2

ID    | actual_checkdate
----- | ----------
1     | 2017-07-30
2     | 2017-07-25
3     | 2017-08-01
4     | 2017-07-15

Expected results

ID    | actual_checkdate | submit_checkdate
----- | ----------       | ----------
1     | 2017-07-30       | 2017-07-31
3     | 2017-08-01       | 2017-07-31
4     | 2017-07-15       | 2017-07-15

basically, I need the results to show all the actual check dates that are within 3 days before and after the submit check date.

not sure if I should join the tables with the interval or do it in the wherr

Answer 1

Add the condition to the join.
on actualcheckdate between dateadd (dd, -3, submitcheckdate) and dateadd (dd, 3, submitcheckdate)
Or whichever way round it should be

Answer 2

Something like.

SELECT * FROM table1 INNER JOIN table2 ON table2.actual_checkdate BETWEEN dateadd (dd, -3, table1.submitcheckdate) AND dateadd (dd, 3, table1.submitcheckdate)