SQL SERVER QUERY

--general idea. Given you need to have candidates who have those 3 qualifications
declare @q1 int, @q2 int, @q3 int
select @q1 =1, @q2 =2, @q3=3

-- this query will give list of candidated who have all 3 qualifications
select c.candidat_id, count(distinct q.qualification_id)
from candidats c join cadidats_qualifications q
      on c.candidat_id=q.candidat_id
where q.qualification_id in (@q1, @q2, @q3)
group by c.candidat_id
having count(distinct q.qualification_id)=3

-- or using profile ID
declare @profileID int, @qCount int
select @profileID =1
select @qCount = count(qualification_id) from profil_qualifications

select c.candidat_id, count(distinct q.qualification_id)
from candidats c join cadidats_qualifications q
      on c.candidat_id=q.candidat_id
where q.qualification_id in (select qualificationid from profil_qualifications where profile_id =@profileID )
group by c.candidat_id
having count(distinct q.qualification_id)=@qCount

Hi there, thanks for your input, however on the first case, i need candidates who have AT LEAST those 3 qualifications. I supposed that in the code you gave, I will only get candidats who have EXACTLY those 3 qualifications.

try

select c.candidat_id, count(distinct q.qualification_id)
from candidats c join cadidats_qualifications q
      on c.candidat_id=q.candidat_id
where q.qualification_id = @q1 or  q.qualification_id=@q2 or q.qualification_id=@q3
group by c.candidat_id
having count(distinct q.qualification_id)>=3

sorry like this

select c.candidat_id, count(distinct q.qualification_id)
from candidats c join candidats_qualifications q
      on c.candidat_id=q.candidat_id
where       exists(select 1 from candidats_qualifications where qualification_id = @q1 and q.candidat_id = candidat_id) and
      exists(select 1 from candidats_qualifications where qualification_id = @q2 and q.candidat_id = candidat_id) and
      exists(select 1 from candidats_qualifications where qualification_id = @q3 and q.candidat_id = candidat_id)
group by c.candidat_id
having count(distinct q.qualification_id)>=3

ralmada suggestion is incorrect since we do not pull any other qualifications  in this query
where q.qualification_id = @q1 or  q.qualification_id=@q2 or q.qualification_id=@q3
, only those 3  qualifications, which means

count(distinct q.qualification_id)
will be 3 or less. It never can be more then 3



--------------------------------------
So here are more explanation on quesries
-- query from 1-st post will give you candidates with exact set of 3 qualifications. You don't need use profileID

-- query from 2-nd post gives you candidate that have ALL qualifications defined in selected @profileID. It will also bring candidates that have other qualification, not defined in profile

-- this query will give you candidate that have at least 3 of qualification from predefined profile, but does not specify which set of 3. So if profile has 5 qualifications, it will bring candidates that have
1234567
135
123,
34567
etc

select c.candidat_id, count(distinct q.qualification_id)
from candidats c join cadidats_qualifications q
      on c.candidat_id=q.candidat_id
where q.qualification_id in (select qualificationid from profil_qualifications where profile_id =@profileID )
group by c.candidat_id
having count(distinct q.qualification_id)=3

>>ralmada suggestion is incorrect since we do not pull any other qualifications  in this query<<
trofimoval, did you read my comment http:#a36893104 ?

Thanks