join - link a value from one table to another and slice one table based on columns from another table in sql

Question

Suppose I have a first table like this:

tbl1:

 eventid       date1         date2
   A          2020-06-21    2020-06-28
   B          2020-05-13    2020-05-24
   C          2020-07-20    2020-06-28

I also have a second table with a quantity and a date:

tbl2:

      quantity       date
        5        2020-06-24
        13       2020-07-24
        8        2020-07-28
        8        2020-06-20
        12       2020-06-27
        9        2020-06-29
        10       2020-05-24
        11       2020-05-12
        18       2020-05-18
        9        2020-05-14
        7        2020-07-18
        12       2020-07-21

Now I want select only the rows from table 2 where the dates fall between the dates of table 1 AND to add a column to table with each row containing A, B or C (eventid from table 1) so that we can see which date in table 2 belongs to which eventid.

So my end result would look like:

      quantity       date       eventid
        5        2020-06-24        1
        13       2020-07-24        3
        8        2020-07-28        3
        12       2020-06-27        1
        10       2020-05-24        2
        18       2020-05-18        2
        9        2020-05-14        2
        12       2020-07-21        3

I've been starring at it for ages now because I need an efficient way to do it..

Is there an efficient way of obtaining the desired result?

question from:https://stackoverflow.com/questions/65944120/link-a-value-from-one-table-to-another-and-slice-one-table-based-on-columns-from

Answer 1

This looks like a join:

select t2.*, t1.eventid
from tbl2 t2 join
     tbl1 t1
     on t2.date >= t1.date1 and t2.date <= t2.date2;