how to filter results from mysql inner join query

Question

I have got a problem with my many to many relationship.

Client table columns: clientID, name, address etc.

User table columns: userID, name, address etc.

Users_clients columns: userID, clientID.

Each client can have multible users attached and users can have multible clients.

Right now my users_clients table contains this:

userID | clientID<br>
1      | 2
1      | 3
2      | 2
2      | 3
3      | 3
3      | 2
4      | 1

On my php page I want a list of users which is not already attached to the client.

Ex. if I click on the client profile which has clientID = 3 It should show me a list of users not attached to this client.

SELECT u.name, u.email, u.userID FROM users u
  INNER JOIN users_clients uc
    ON u.userID = uc.userID 
  WHERE uc.clientID !=3

This works fine, but how do I create my MySQL query so that if a user have both clientID 3 and client 2 it doesn't show.

This query shows

1|2 2|2 3|2 4|1

and what I'm seeking is:

3|2 4|1

Hope this makes sense to you. Feel free to ask :)

Answer 1

You could process differently.

First make a request to seek every user with a clientId = 3;

select u.id from users u
INNER JOIN users_clients uc
ON u.userID = uc.userID 
WHERE uc.clientID =3;

And then you could use this request to fetch all the users except those who have a clientId = 3.

Final request looks like this :

select u.id from users u where u.id NOT IN (select u.id from users u
INNER JOIN users_clients uc
ON u.userID = uc.userID 
WHERE uc.clientID =3);

hope this'll help;