I have below table with 3 column. Lets say table1 with the following data
+---------+-----------------------+----------+ | ID | E_Date | TimeDiff | +---------+-----------------------+----------+ | 7390709 | 2020-03-26 22:25:30 | 456 | +---------+-----------------------+----------+ | 7390707 | 2020-03-26 16:25:30 | 3 | +---------+-----------------------+----------+ | 7390706 | 2020-03-26 19:25:30 | 3 | +---------+-----------------------+----------+ | 7390701 | 2020-03-26 16:23:53 | 512419 | +---------+-----------------------+----------+ | 7390696 | 2020-03-26 14:33:08 | 111034 | +---------+-----------------------+----------+ | 7390681 | 2020-03-20 15:25:30 | 2 | +---------+-----------------------+----------+ | 7390680 | 2020-03-20 16:25:30 | 63867 | +---------+-----------------------+----------+ | 7390679 | 2020-03-20 13:25:30 | 63867 | +---------+-----------------------+----------+ | 7390675 | 2020-03-20 12:30:46 | 63867 | +---------+-----------------------+----------+
Another table with 4 columns, lets say table2 with following data
+-----------------------+---------------------+--------+------+ | Start_Date | End_Date | Typ_ID | Diff | +-----------------------+---------------------+--------+------+ | 2020-03-26 18:00:01 | 2020-03-26 21:00:00 | 3 | 180 | +-----------------------+---------------------+--------+------+ | 2020-03-26 13:40:01 | 2020-03-26 17:30:00 | 1 | 230 | +-----------------------+---------------------+--------+------+ | 2020-03-26 10:30:01 | 2020-03-26 13:30:00 | 1 | 180 | +-----------------------+---------------------+--------+------+ | 2020-03-26 06:10:01 | 2020-03-26 10:00:00 | 1 | 230 | +-----------------------+---------------------+--------+------+ | 2020-03-20 18:00:01 | 2020-03-20 21:00:00 | 1 | 180 | +-----------------------+---------------------+--------+------+ | 2020-03-20 13:40:01 | 2020-03-20 17:30:00 | 3 | 230 | +-----------------------+---------------------+--------+------+ | 2020-03-20 10:30:01 | 2020-03-20 13:30:00 | 1 | 180 | +-----------------------+---------------------+--------+------+ | 2020-03-20 06:10:01 | 2020-03-20 10:00:00 | 1 | 230 | +-----------------------+---------------------+--------+------+
What I would like to achieve is check in Table2 for start_date and End_Date where typ_Id = 1 from Table1 where E_Date comes under in Table2 Start_Date and End_Date.
for ID 7390706, 7390681,7390680 the E_Date comes under the range in table2 Start_Date and End_Date where Typ_ID=3 and for ID 7390709 there is no Start_Date and End_Date exist so those rows has be excluded from the final result.
So the final table has to be like this.
+---------+-----------------------+----------+ | ID | E_Date | TimeDiff | +---------+-----------------------+----------+ | 7390707 | 2020-03-26 16:25:30 | 3 | +---------+-----------------------+----------+ | 7390701 | 2020-03-26 16:23:53 | 512419 | +---------+-----------------------+----------+ | 7390696 | 2020-03-26 14:33:08 | 111034 | +---------+-----------------------+----------+ | 7390679 | 2020-03-20 13:25:30 | 63867 | +---------+-----------------------+----------+ | 7390675 | 2020-03-20 12:30:46 | 63867 | +---------+-----------------------+----------+
I am still learning joins so any help will be appreciated.
http://sqlfiddle.com/#!18/6eb43
Advertisement
Answer
Try the following and here is the demo.
select
id,
e_date,
timediff
from tableA
join tableB
on e_date between Start_Date and End_Date
where typ_id = 1
output:
| id | e_date | timediff | | ------- | ------------------------ | -------- | | 7390707 | 2020-03-26 16:25:30 | 3 | | 7390701 | 2020-03-26 16:23:53 | 512419 | | 7390696 | 2020-03-26 14:33:08 | 111034 | | 7390679 | 2020-03-20 13:25:30 | 63867 | | 7390675 | 2020-03-20 12:30:46 | 63867 |