Query with Left Join considering the most recent records from the auxiliary table

Question

I have two tables:

curriculo (cur_codigo,cur_nome)
lembrete_envios (lem_codigo,lem_curriculo,lem_data,lem_tipo)

• The table resume stores candidate ID and candidate name

• The table reminete_shipments stores reminders sent to resumes

• lem_resume is the foreign key of cur_code

• Each curriculum may contain one or more reminders.
• When the resume has more than one linked reminder, the survey should consider the date of the most recent reminder.

I’m struggling to assemble a survey that returns the resumes that meet two conditions, as detailed below:

The query should return the curricula that have no reminders sent or that have sent reminders, the most recent being 7 days before the current day.

TEST:

 INSERT INTO curriculo (cur_codigo, cur_nome) VALUES
   ('1', 'A'),
   ('2', 'B'),
   ('3', 'C');

 INSERT INTO lembrete_envio (lem_codigo,lem_curriculo,lem_data,lem_tipo) VALUES
   ('1', '1', '2020-01-01', '1'),
   ('2', '1', '2020-01-05', '1'),
   ('3', '1', '2020-02-08', '1'),
   ('4', '3', '2020-01-19', '1');

For this example, let’s consider the current date as being (2020-02-10).

In this case, the consultation should return the curricula: B and C, because

• B (has no linked reminders)
• C (the linked reminder was sent more than 7 days from the current date (10 is less than 18)

Obs: To cannot be included in the results as your most recent reminder (2020-02-08) is not less than 7 days from the current date.

 SELECT curriculo.cur_nome, max(lembrete_envio.lem_data)
 FROM curriculo LEFT OUTER JOIN lembrete_envio
 ON (curriculo.cur_codigo = lembrete_envio.lem_curriculo)
 WHERE lembrete_envio.lem_data BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 
 DAY) AND CURRENT_DATE()
 OR lembrete_envio.lem_data IS NULL
 GROUP BY curriculo.cur_codigo;

http://sqlfiddle.com/#! 9/a320d/8

Answer 1

If the field lem_datais effectively of the type date then try:

SELECT curriculo.cur_nome, max(lembrete_envio.len_data)
FROM curriculo LEFT OUTER JOIN lembrete_envio ON (curriculo.cur_codigo = lembrete_envio.lem_curriculo)
WHERE lembrete_envio.lem_data BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()
   OR lembrete_envio.lem_data IS NULL
GROUP BY curriculo.cur_codigo;