Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005

    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
    limit 100;
    Attached Thumbnails Attached Thumbnails Tables.png  

  2. #2
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    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.

  3. #3
    Join Date
    Sep 2005
    Thank you for the reply dave. My problem is, base from my existing sql query how will I update it to get the result (same as the table with red border in the screenshot attached) I need.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts