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

Asked

Viewed 140 times

2

I have the following tables:

inserir a descrição da imagem aqui

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.

  • the "id" of each table is the primary key

3 answers

3

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

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.

  • it is possible to make this query but present the dates of the tables also?

  • You mean, present the dates of the last votes and appeals, if any?

  • select u.*, (select MAX(v1.data) from tbl_votes v1.id_user = u.id) as "Date Vote", (select MAX(R1.data) from tbl_recursos R1 Where R1.id_user = u.id) as "Date Resource"

  • What I want is to show users that have no resources and votes in the last 5 days (this makes your query),now I also wanted to show the date of the last time that user made a resource and voted if possible.

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 believe that this would be the best way.

  • your query does not return any value, and I have users in this condition

  • I edited the query by adding a group by id

  • I think your query has a resso, it’s matching tb.id = tb4.id_user. Shouldn’t it be tb.id_user= tb4.id_user? thank you

  • id_user num matches the id of the table tbl_users no?

  • No, I think it’s okay. I’m the one who messed up. Thank you

  • 1

    If your bank is fully indexed and with the appropriate foreign keys, Lucas' response will be faster and less costly for the bank, otherwise Sertage’s better

Show 1 more comment

Browser other questions tagged

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