I am working on generating a report for an attendance application, but I’m having some query issues. I need to select the person’s precinct, name, address, and whether they were marked as present or not. I also need to include excused absences in the report, but the absences show up when I choose a specific precinct. So, let’s say the precinct is
02-08 but the absence is in precinct
10-00. Both are showing in the same query and I don’t want that.
I need the absence to show in the
10-00 query (which it does, but I only want it to show in the
10-00 query, not any of the others. So if there is no absence in
02-08, then don’t include it).
Here is the current query I have so far. What am I doing wrong?
SELECT precinct , CONCAT(last_name, ', ', first_name, ' ', middle_name, ' ', IFNULL(suffix, ' ')) as full_name , residential_address , IF(at.member_id IS NULL, 'No', 'Yes') as 'present' , IF(ab.member_id IS NULL, 'No', 'Yes') as 'absent' FROM members m LEFT JOIN attendance at ON at.member_id = m.id LEFT JOIN absence ab ON ab.member_id = m.id WHERE m.precinct = '02-08' AND at.present = 1 OR ab.absent = 1 ORDER BY m.precinct, m.id
Here are the corresponding table structures:
(Normally I would include a screenshot, but my Snipping Tool isn’t working)