Unanswered: Query From Multiple Table with Group By
I need you help here. I have 5 tables as show in the picture attached the table with the red border is the result I need. Basically, I want to get all the Logs (time IN and OUT of the student) that the Guardians are not notified yet. The guardian must be notified of the first time IN and the last timeout of the student at a given time. I also have the Notification Logs table to store all the logs that are already been notified to the guardians so that it will not be notified again.
Currently I have this query but it does not give the output I need. Thanks in advance!
select l.LogID as 'Log ID', s.Firstname as 'sFirstname', s.Gender as 'sGender',
r.Relationship as 'Relationship', l.TransDateTime as 'TransDate' , l.Direction as 'Direction',
r.GuardianID as 'Guardian ID'
from logs l, recipient r, student s
where date(l.TransDateTime) = date(curdate()) AND
l.StudentID = r.StudentID AND
l.StudentID = s.StudentID AND
not exists (select n.LogID from notification_logs as n where n.LogID = l.LogID AND n.GuardianID = r.GuardianID AND n.NotifyVia = 1)
group by l.TransDateTime
why are you putting a group by? There are no aggregate functions where you would need a group by. Other than that you didn't even specify what problem(s) you are having. A cursory look at your SQL the rest seems fine. Please be a bit more specific about what your problem might be and how we can help you.