Find out if people did not attend

Asked

Viewed 51 times

1

I have a table called lockers that record the locker number , name of the owner , and I have another table that record the entrance of students, but if someone who has closet missing for 10 days I need to warn, I can bring everyone who attended the period, how to find out who did not attend ? 

SELECT idcad,data_presenca FROM armario
INNER JOIN presenca ON armario.idcad = presenca.id_cad 
WHERE data_presenca BETWEEN CURRENT_DATE()-10 AND CURRENT_DATE()
mysql

2 answers

1

From what I understand:

SELECT armario.idcad FROM armario
LEFT OUTER JOIN presenca ON armario.idcad = presenca.id_cad 
WHERE data_presenca < CURRENT_DATE()-10

0


Try with subselect and not exist

SELECT armario.idcad 
FROM armario
Where not exists (select null
                  From presenca 
                  Where armario.idcad = presenca.id_cad 
                  And data_presenca > CURRENT_DATE()-10)

The lower select checks for an "idcad" who had presence in the last 10 days , date greater than "today" minus 10 , the top that is not in this group "Not", ie their missing , people who apparently lost the closet ... :)

  • It worked fine, but I still have doubts about how the code works, but it has helped me a lot and I search here more on the subject, Thank you very much !

  • I edited the text , see there.

  • Buddy, there was a problem, this code worked right until the life of the month, but now he’s not considering whether the staff was present last month, where the problem may be ?

  • I don’t see why things should go wrong in the turn of the month. Here’s an example ?

Browser other questions tagged mysql

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