Select the records that are not in another table

0

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
  )
    
asked by Stilgar 26.03.2018 в 00:59
source

1 answer

2

You can use the first query within WHERE of the second:

SELECT *
FROM users
WHERE id NOT IN
  (
    SELECT users_id
    FROM projects_users
    WHERE projects_id = 8;
  );
    
answered by 26.03.2018 / 01:15
source