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 :)
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;
Have you tried a DISTINCT instruction ?
select distinct u.id from users u
INNER JOIN users_clients uc
ON u.userID = uc.userID
WHERE uc.clientID =3;