Search code examples
sqlforeign-keysmany-to-manyone-to-many

SQL query to find users that don't have any subscription to a specified list (many-to-many)

Having two tables, "users" and "lists", and a many-to-many "subscriptions" table relating users to lists (thus having foreign keys user_id and list_id), what would be a single SQL query to find all the users that don't have any subscription with a specific list_id (naturally including the users that have no subscriptions at all)?

Solution

  • Time to break out not exists again:

    select
  u.user_id
from
  users u
where
  not exists (
    select 1 from subscriptions s where s.user_id = u.user_id and s.list_id = N
  )