Inner join with special conditions

Given an hourly table A with full records, e.g.:

User    Hour  Purchase 
Joe      1       0
Joe      2       0
Joe      3       0
Joe      4       1
Joe      5       0
Joe      6       0
Joe      7       0
Joe      8       1
Joe      9       1
Joe     10       0 

And a subset B of it, e.g.

User    Hour    Purchase 
Joe      3         0
Joe      9         1
Joe     10         0

I want to keep only those records from A that are in B or at most 2hr behind the B subset, without duplication, e.g.

User    Hour Purchase 
Joe      1    0
Joe      2    0
Joe      3    0
Joe      7    0
Joe      8    0
Joe      9    1 
Joe     10    0

How can the result be achieved with an inner join, without duplication (in this case the hours 8&9) and preserving the right purchase values for the hours in B? (This is an MWE, assume multiple users and timestamps instead of hours)

Answer

This is a simple INNER join with the proper conditions in the ON clause:

SELECT DISTINCT a.*
FROM a INNER JOIN b
ON b.User = a.User AND a.Hour BETWEEN b.Hour - 2 AND b.Hour

You can add a WHERE clause if you want results for a specific user:

WHERE a.User = 'Joe'