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
Advertisement
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>);