I have two tables, lets call them A and B. Table A has data regarding specific events and has a unique key column pairing of event_date and person. Table B has aggregate data over time and thus has key columns start_date,end_date and person. The date ranges in table B will never overlap for a given person so end_date is not strictly necessary for the composite key.

Below are two examples

SELECT event_date, person
FROM A

SELECT start_date, end_date, person, attribute
FROM B

I would like to add the attribute column to table A. The merger should consider in which date range the event_date column falls into and choose the appropriate attribute. The final table after the merge should look like this:

How would one go about solving this?

CodePudding user response：

You can try to JOIN by BETWEEN dates.

SELECT a.*,b.attribute
FROM A a
JOIN B b
ON a.event_date BETWEEN b.start_date AND b.end_date
AND a.person = b.person