need help with join query/excluding? query?

[The14thGOD]

I'm having troubles figuring out how to do this (never done this before) and I think I'm also having a brain fart at the same time.

Basically I'm trying to grab people who are on a project, and exclude those from another query to get others who are tied to the client but not already on the project.

 

Here's my query so far..

SELECT DISTINCT n.id,n.name FROM (
SELECT u.id,u.client_id,u.name 
FROM users AS u 
RIGHT JOIN projects AS p ON p.client_id = u.client_id 
WHERE p.id = 4 AND u.status='enabled'
) AS n 
INNER JOIN project_relationships AS pr 
ON pr.user_id = n.id 
WHERE pr.project_id = 4 
AND n.id != pr.user_id

 

The results are nothing, the inner query:

SELECT u.id,u.client_id,u.name 
FROM users AS u RIGHT JOIN projects AS p 
ON p.client_id = u.client_id 
WHERE p.id = 4
AND u.status='enabled'

 

Results in both users tied to client:

id | name

1 | justin - THIS is the only user on the project

2 | jayjay

 

So basically, i want to grab jayjay only.

 

Sorry if I'm not explaining this well, I'm not exactly a mysql expert..

 

Thanks for any and all help,

Justin

 

*edited for readability*

 

[Barand]

Can you post some sample data for those three tables so we can get a better picture of the relationships?

[The14thGOD]

Users:

id | client_id | name

1 | 1 | justin

2 | 2 | laura

3 | 1 | jayjay

 

clients:

id | name

1 | client 1

2 | client 2

 

projects:

id | client_id | name |

1 | 1 | project 1

 

project_relationships:

id | user_id | client_id | project_id

1 | 1 | 1 | 1

 

There's a lot more columns, but nothing else that is related to this query in particular.

 

This query almost worked.. but it basically matches every other one (so it still pulls in both records), worked when 'justin' was assigned to the project and 'jayjay' wasn't. I'm assuming it can be written better though w/ less crap.

SELECT n.id,n.client_id,n.name 
FROM (
SELECT u.id,u.client_id,u.name 
FROM users AS u 
RIGHT JOIN projects AS p 
ON p.client_id = u.client_id 
WHERE p.id = 4 AND u.status='enabled'
) AS n 
INNER JOIN projects as j 
ON j.client_id = n.client_id 
INNER JOIN project_relationships AS pr 
ON pr.client_id = n.client_id 
WHERE pr.project_id = j.id 
AND pr.user_id != n.id

[The14thGOD]

I decided to drop what I had and try to approach it a bit differently, found a NOT IN clause, this seems to do the trick (for now knowing my luck...)

 

SELECT u.id,u.client_id,u.name

FROM users AS u

RIGHT JOIN projects AS p ON p.id = 4

WHERE u.id NOT IN (SELECT user_id FROM project_relationships WHERE project_id = 4)

AND p.client_id = u.client_id

AND u.status='enabled'