2
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.
2
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.
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
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
Browser other questions tagged php mysql sql
You are not signed in. Login or sign up in order to post.
the "id" of each table is the primary key
– pc_oc