Search results in 3 tables with the date of the last 5 days

Question

I have the following tables:

What I’m trying to do is get the user name that has no record on tbl_votos and no recourse in tbl_recursos in the latter 5 days. I’m using php and mysql.

Answer 1

Another alternative, making LEFT JOIN and checking which returned null:

SELECT
    u.nome
FROM
    tbl_utilizadores AS u
    LEFT JOIN tbl_recursos AS r
        ON r.id_user = u.id AND r.data > DATE_SUB(CURDATE(), INTERVAL 5 DAY)
    LEFT JOIN tbl_votos AS v
        ON v.id_user = u.id AND v.data > DATE_SUB(CURDATE(), INTERVAL 5 DAY)
WHERE
    r.id IS NULL
    AND v.id IS NULL

Answer 2

I think the best way would be using a not exists, as follows:

select *
  from tbl_utilizadores u
 where not exists (select 1 from tbl_votos v where v.id_user = u.id and v.data > (NOW() - interval 5 day) )
   and not exists (select 1 from tbl_recursos r where r.id_user = u.id  and r.data > (NOW() - interval 5 day))

I just don’t know how to get the date right.

To put the date of the last vote and feature, just put in select:

select u.*, (select MAX(v1.data) from tbl_votos v1.id_user = u.id) as "Data Voto", (select MAX(r1.data) from tbl_recursos r1 where r1.id_user = u.id) as "Data Recurso"

from from on it is equal.

Answer 3

SELECT nome, COUNT(tb2.id) recursos, COUNT(tb3.id) votos
FROM tbl_utilizadores tb
LEFT JOIN tbl_recursos tb2 ON tb.id = tb2.id_user AND tb2.data > (NOW() - interval 5 day)
LEFT JOIN tbl_votos tb3 ON tb.id = tb3.id_user AND tb3.data > (NOW() - interval 5 day)
GROUP BY tb.id
HAVING recursos = 0 AND votos = 0

I believe that this would be the best way.