How to select where there isn’t an entry in pivot table relating to X id

In my database I have a table of notifications types that looks something like:

+----+---------+
| id | type    |
+----+---------+
|  1 | live    |
|  2 | warning |
+----+---------+

A table for events:

+----+---------------+--------------------+
| id | name          | description        |
+----+---------------+--------------------+
|  1 | My event      | Hello              |
|  2 | Other Event   | I'm an event!      |
+----+---------------+--------------------+

And a pivot table event_called_notifications to keep track of what type of notification have been called already for each event:

+----+----------+----------------------+
| id | event_id | notification_type_id |
+----+----------+----------------------+
| 22 |        1 |                    1 |
| 23 |        2 |                    1 |
+----+----------+----------------------+

I would like to select all the events that don’t haven’t have a particular type of notification called, IE entries that don’t have a pivot relating to X

Answer

One option is to use NOT EXISTS and a correlated subquery that checks for a row in the linking table for the event and notification.

SELECT *
       FROM events e
       WHERE NOT EXISTS (SELECT *
                                FROM event_called_notifications ecn
                                WHERE ecn.event_id = e.id
                                      AND ecn.notification_type_id = <your X goes here>);