Query for data that is not there

I have this setup

attendance table:
------------------------------------------
| member_id | attendance_date | event_id |
------------------------------------------

attendance indeces
---------------------------------------------
| p1 (member_id, attendance_date, event_id) |
| p2 (attendance_date, member_id, event_id) |
| member (member_id)                        |
| total (attendance_date, event_id)         |
---------------------------------------------

members table
-------------------------------------------------
| member_id | department_id | registration_date |
-------------------------------------------------

members indeces:
-----------------------------------------------
| PK (member_id)                              |
| registration (registration_date, member_id) |
| department (department_id, member_id)       |
-----------------------------------------------

So basically this is an attendance system. And I need to run reports based on the attendance table. I've figured out much of the queries needed except for the part where I need to know how many absences a member has for a given time. I pretty much know how to query for data that is there. But I do not know how will I query for data that is not there (absences).

I tried this:

SELECT
  attendance_date,
  COUNT(DISTINCT event_id)
FROM
  attendance
WHERE
  WEEKOFYEAR(attendance_date) BETWEEN 27 AND 31
GROUP BY
  WEEKOFYEAR(attendance_date)

which gives me the number of events a member should've been able to attend based on other's attendance record, but the problem is when there is an event with no attendees at all (unlikely) or when there is a newly registered member.

I tried this:

EXPLAIN SELECT
     m.member_id AS id,
     COUNT(DISTINCT a.event_id)
   FROM
     members AS m
     LEFT JOIN
       attendance AS a
       ON
         m.registration_date < a.attendance_date AND
         WEEKOFYEAR(a.attendance_date) BETWEEN 27 AND 31
   WHERE
     m.department_id = 1

But `E

Why are you determining a member's absence through another member's presence?

I guess there must be an events table in your schema. And I would expect it to have a column called something like event_date. It's the event_date column that you should use to determine which events a member could visit (members.registration_date < events.event_date).

After getting the list of members and their respective valid events, you can anti-join it to the attendance table to see which member missed which (or how many) events.

Here's an example query illustrating what I just said. It implements the anti-join using LEFT JOIN + WHERE IS NULL check:

SELECT
  m.member_id,
  e.event_id
FROM       members    m
INNER JOIN events     e ON m.registration_date < e.event_date
LEFT  JOIN attendance a ON m.member_id = a.member_id
                       AND e.event_id  = a.event_id
WHERE a.member_id IS NULL
  AND ... /* some other conditions to filter members and/or events, if necessary */

Or here's another example showing how you could count total events available to a member and those actually attended by him/her:

SELECT
  m.member_id,
  COUNT(e.event_id) AS total_events,
  COUNT(a.event_id) AS attended_events,
FROM       members    m
INNER JOIN events     e ON m.registration_date < e.event_date
LEFT  JOIN attendance a ON m.member_id = a.member_id
                       AND e.event_id  = a.event_id
WHERE /* some conditions as necessary */

Note that in the last example there's no IS NULL check. It's because that query needs to get all the events per member. Missed events are just not counted by the COUNT() function (because the corresponding a.event_id value contains NULL in those cases).

UPDATE

Since, as per your comment, the events are recurring (and thus have no fixed dates), I would suggest adding and using an event_calendar table, populated beforehand either manually or programmatically. Naturally, the events table in the above examples would be replaced with event_calendar.

Alternatively, if maintaining an event calendar is not an option, you could replace events in your queries with

(
  SELECT DISTINCT
    event_id,
    attendance_date AS event_date
    /* or, if they are not purely dates:
    CAST(attendance_date AS date) AS event_date
    */
  FROM attendance
) e

(basically, @ypercube's suggestion with dates thrown in).

StackExchange Database Administrators Q#22272, answer score: 4