I have the following tables:
What I'm trying to do is fetch the names of users who have no records in tbl_votos
and no resources in tbl_recursos
in the last 5 days . I'm using php and mysql.
I have the following tables:
What I'm trying to do is fetch the names of users who have no records in tbl_votos
and no resources in tbl_recursos
in the last 5 days . I'm using php and mysql.
I think the best way would be to use 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 do not know if the way to get the date is correct.
To put the date of the last vote and appeal, just put in the 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 now on it stays the same.
Another alternative, doing 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
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 think this would be the best way.