shqnks shqnks - 1 year ago 134
MySQL Question

Sql Request? Where clause

First of all, sorry for my english, I'm from france.

I try to make a simple SQL request, but I'm stucked, and I don't know why. So here are a simplification of what I want to do:

2 tables:

user_profil: iduser , idprofil

profil : idprofil, profil

What I want:

I'd like to get the list of the profils which one user don't have. So if a user have a profil in user_profil, I don't want to have this profil in the result of the request.

Infact, if the user have the profil, I don't want this profil to be in the result of the request.

I tried:

select distinct (p.idprofil), p.profil
from profil p, user_profil u
where p.idprofil != u.idprofil and u.iduser = X

But it doesn't work (it works if a user have only one profil, but if he has at least 2 profils, it doesn't work.)

Answer Source

Use not in or not exists:

select u.*
from user_profil u
where u.idprofil not in (select p.idprofil from profil p);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download