MySQL Possible IF Statement?

Databases
1

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:

Members: https://snipboard.io/ln9oj6.jpg

Attendance: https://snipboard.io/9OHXdu.jpg

Absence: https://snipboard.io/G0LltT.jpg

(Normally I would include a screenshot, but my Snipping Tool isn’t working)

2

If I understand the question, you’ll want to wrap the OR conditions, though changing the JOIN would be better…

The Or Conditions…

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

The Join…

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 AND at.present = 1
  LEFT JOIN absence    ab ON ab.member_id = m.id AND ab.absent = 1
 WHERE m.precinct = '02-08' 
 ORDER BY m.precinct, m.id
3

Thanks for the tip! But, if I may ask, why would changing the JOIN be better if changing the OR conditions produces the same result? Not trying to argue any reasoning, just curious as to why…

4

this is actually a bit more complex than you might think

putting a condition into the ON clause of a left join is a lot different from putting that condition into the WHERE clause

in the WHERE clause, you’re saying the condition has to be true, and therefore not NULL, and thus the left join is functionally equivalent to an inner join

in the ON clause, you’re saying to look for rows with that condition, but if there aren’t any, return the row from the left table anyway

that said, i’m not sure that with two conditions ORed together, the NULL one one side or the other will still pass

you must simply test both ways, and confirm they produce the same results

as an aside, did you know that you can rewrite AND (at.present = 1 OR ab.absent = 1) as AND 1 IN (at.present,ab.absent) – they work the same way

3 Likes
6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.