CODEWITHSUNDEEP
mysqlsql
jemminger
jemminger

Reputation: 5173

SQL query to return users with no active group memberships

I've got this schema, which has the ability to soft-delete/archive a group:

create table users (id int, name varchar(255));
create table groups (id int, name varchar(255), archived_at datetime);
create table memberships (group_id int, user_id int);

insert into users (id, name) values 
  (1, 'no groups'),
  (2, 'only active groups'),
  (3, 'only archived groups'),
  (4, 'active and archived groups');

insert into groups (id, name, archived_at) values
  (1, 'active group', null),
  (2, 'archived group', '2021-04-13');

insert into memberships (user_id, group_id) values
  (2, 1),
  (3, 2),
  (4, 1),
  (4, 2);

And I want to query users that have "no groups": exactly zero Active (non-archived) groups, plus zero or more Archived groups.

-- How do I get this to return only users 1 and 3?
select *
from users u
left join memberships m on m.user_id = u.id
left join groups g on g.id = m.group_id
where
  m.group_id is null
  or 
  g.archived_at is not null

Is this possible with the current schema?

https://www.db-fiddle.com/f/h1szvQmQHp1g84Ex9WvRHy/1

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

This sounds like not exists:

select u.*
from users u
where not exists (select 1
                  from memberships m join
                       groups g
                       on m.group_id = g.id
                  where m.user_id = u.id and g.archived_at is null
                 );

Presumably, an active group is one that has not been archived.

Upvotes: 1

Related Questions