Select only records without future dates via join-CodePudding

There are 2 tables: events and dates. One event could have multiple dates.

I would like to select only those events which don't have corresponding dates in the future.

I tried the following query

SELECT e.id_event FROM events e RIGHT JOIN dates d ON e.id_event=d.id_event AND !(d.date > NOW())

but it shows events which has both dates in past and in future.

Sample data

Events table

id_event
1
2

Dates table

id_event	date
1	2020-10-10
1	2030-10-10
2	2020-10-10

The query should return only one record, id_event = 2.

CodePudding user response：

A couple of ways to do this

create table t1(id_event int, date date);

insert into t values (1),(2);
insert into t1 values (1,'2020-01-01'),(1,'2030-01-01'),(2,'2020-01-01');

SELECT e.id_event 
FROM t e 
left JOIN t1 d ON e.id_event=d.id_event 
group by e.ID_event having max(d.date) < now()
;

SELECT e.id_event 
FROM t e 
left JOIN t1 d ON e.id_event=d.id_event 
where not exists(select 1 from t1 t2 where t2.id_event = d.id_event and t2.date > now())
;

First may not be useful if you intend to take more columns from either table..

CodePudding user response：

You can try this query (Demo):

SELECT evt.id_event
FROM events evt 
WHERE evt.id_event NOT IN (
  SELECT e.id_event 
  FROM events e 
  INNER JOIN dates d 
  ON e.id_event=d.id_event 
  AND d.date > CURDATE()
);

Here, at first, all event ids with future dates are found out, and then event ids are selected which are not in the previously found out event ids.

CodePudding user response：

NOW()is timestamp : you want CURDATE try

SELECT e.id_event 
FROM events e RIGHT 
JOIN dates d 
ON e.id_event=d.id_event AND d.date <= CURDATE();

<= CURDATE() is before or including today which is the same thing as NOT IN THE FUTURE.