join on id where a date does not exist for that group-CodePudding

Let's say that i have two tables. t1 has id, current_date, previous_date columns. t2 has id and date column. I want to return the ids where the date range between current and previous date for any record for that particular group of t1 does not correspond to any date for that group on the t2. T1 and T2 tables:

id	current_date	previous_date
1	2021-09-25	2021-09-28
1	2021-10-25	2021-10-27
2	2021-01-01	2021-01-05

id	date
1	2021-09-27
2	2021-01-03

Expected result below

id
1

I ran the code below

SELECT *
FROM t1
INNER JOIN t2 (ON t1.id = t2.id 
AND NOT EXISTS (SELECT t1.date from t1 where t1.date between t2.current_date AND t2.previous_date))

However, i think the query runs forever and does not return me what i want.

CodePudding user response：

You can evaluate the table2 date in the where condition.

Try:

SELECT t1.*
FROM t1
INNER JOIN t2 ON t1.id = t2.id 
where t2.`date` not between t1.`current_date` and t1.previous_date

https://www.db-fiddle.com/f/uLPPE1DoKjKYBoSXfKahJN/23

Note that I used MySQL for demonstration and in MySQL date and current_date are reserved keywords, they should be put in backticks.

CodePudding user response：

You can use check if your date does not occur between the previous and current dates in other table, to check your condition:

SELECT 
  t2.*
FROM
  table2 t2 
WHERE  t2.`id`  IN  
  (SELECT 
    t1.`id`
  FROM
    table1 t1 
  WHERE t2.`date` NOT BETWEEN t1.`current_date` AND t1.`previous_date` );