Search results for 3 table with date of last 5 days

2

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.

    
asked by anonymous 08.05.2015 / 15:08

3 answers

2

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.

    
08.05.2015 / 15:14
3

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
    
08.05.2015 / 16:27
0
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.

    
08.05.2015 / 15:19