how to get not matched record in SQL-CodePudding

i have a tables like below

tableName: users

tableName: projects

tableName: user_project

So i want to get users who are not a part of project "1".

i tried like this but no luck

SELECT u.id, u.username
FROM testdb.user_project up
LEFT JOIN testdb.users u
ON up.userId = u.id
WHERE up.projectId=1 AND u.id IS NULL

My expected output

id  | username
--------------
3   | u3
4   | u4

can you please help me out of this

Thank you in advance

CodePudding user response：

The below query should work

select u.*
from users u
where u.id not in (select userId from user_project where projectId = 1)

CodePudding user response：

use not exists

select u.* from users u     
where not exists ( select 1 from user_project up
      where up.userid= u.id and up.projectid=1)

CodePudding user response：

You can use a left join to find all the users not on project 1 but you also need DISTINCT to avoid getting duplicate results.

SELECT DISTINCT users.id, users.username
FROM users
LEFT JOIN user_project ON (users.id = user_project.userId)
WHERE user_project.projectId <> 1 or user_project.projectId is null

CodePudding user response：

Basically you want the users that are not associated with Project 1 in the relation table. You can do that using "where not exists".

The solution is better, cause exists removes the need to take care of duplicate values, cause one single occurrence is enough to match (or not match) the definition.

select distinct
    u.id
  , u.username
from users u
where not exists (
  select
    1
  from user_project up
  where 1=1
    and up.projectID = 1
    and up.userId = u.id
)

CodePudding user response：

SELECT u.id, u.username
FROM testdb.user_project up
LEFT JOIN testdb.users u
ON up.userId = u.id
WHERE up.projectId <> 1;