This is the logic in a project involved many users and a user participates in many projects, I have these tables
projects
id |name |
---|-----------|
8 |Proyecto 1 |
9 |Proyecto 2 |
projects_users
id |projects_id |users_id |role |
---|------------|---------|-----|
1 |8 |1 |1 |
2 |8 |3 |0 |
3 |8 |4 |0 |
4 |9 |8 |0 |
users
id |user_name |
---|----------|
1 |root |
3 |Israel |
4 |Hugo |
8 |carmen |
9 |alfredo |
What I need is the users that do not participate in a project, for example the users that do not participate in Project 1, which would result in
id |user_name |
---|----------|
8 |carmen |
9 |alfredo |
what I did in two consultations
SELECT id FROM projects_users WHERE projects_id = 8;
SELECT * FROM users WHERE id NOT IN (1, 3, 4);
I'm looking for a way to do it with a query,
Thanks Gerry using nested query you get the result
SELECT *
FROM users
WHERE id NOT IN
(
SELECT id
FROM records_users
WHERE records_id = 8
)