Filtering user in a group but does not take part in a project-CodePudding

I have 3 tables user, group and project. One user can only join a single group but can joins multiple projects. So I have a member table for many-many relationship between user and project. It has 2 foreign keys point at user_id and project_id. Table user has group_id pointing to group.id Table project has group_id pointing to group.id

I just wanna retrieve list of user who in a group but does not take part in a project. By far I just try an left join between user and member tables as following query:

SELECT "users".* FROM "users" LEFT JOIN members ON users.id = members.user_id 
WHERE ( users.group_id = 1 AND 
(members.project_id != 2 OR members.project_id is NULL)AND users.is_disabled is FALSE) 
ORDER BY id ASC

For example an user in a group 1 but joining project 1 and project 2. And I wanna filter list of user in group 1 but do not join project . Then this query still return this user in example since one of his project_id is 1. Any solution for this scenario ? Many thanks!

CodePudding user response：

I found an answer, I just get a list of user have project_id = 1 and group id = 1. Then select a list user of group_id = 1 and plus where user is not in the list of the first list.

CodePudding user response：

A simple WHERE predicate can check if the user belongs to a group. Now, to check the user does not participate in a project you can use NOT EXISTS.

The query can look like:

select *
from users u
where group_id = 123 -- check the user belongs to group 123
  and not exists (
    select 1 from members m where m.user_id = u.user_id
      and m.project_id = 456 -- check not in project 456
  )