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

Asked

Viewed 50 times

-1

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

mysql join

  • Ask me a question: how would you do if, for example, the current day was 05?

  • I had done so just to expose the problem here, but I’ve updated the field to date type here in question.

1 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;

Browser other questions tagged mysql join

You are not signed in. Login or sign up in order to post.