Finding specific posts in a many-to-many sql query

[rawe]

Hi everybody

I have a database with a collection of persons. Each person can have a number of competences. I'm doing this with three tables: one for the persons, one for the different competences and one to join the two tables together in a many-to-many relation.

The tables are (in a simplified version, that is):

tblPerson:
PersonID
SurName
LastName
...

tblCompetence:
CompetenceID
CompetenceName
...

tblPersonCompetence:
PersonCompetenceID
Person_ID
Competence_ID

What I would like to be able to do is make a query that selects all the persons who have a precise combination of competences. I would in other words be able to find all the persons who individually have (at least) the following competences: drivers license, high school degree and black hair (just as an example...).
The persons might have other competences as well, but that's not important.

I can (of course) make querys that retrieve all persons with a specific competence and all persons without that competence, I can retrieve all competences that a certain person have etc. etc. etc.
But I do not seem to be able to make a query that retrieve all persons that all have (at least) a specific combination of one or more competences.

I've tried subqueries and more - but nothing seems to be able to work.
Tips appreciated!

Best

Rasmus Wehner
Mimia, Denmark

 

[PHV]

A starting point:
SELECT P.PersonID, P.SurName, P.LastName
FROM tblPerson P
INNER JOIN tblPersonCompetence J ON P.PersonID = J.Person_ID
INNER JOIN tblCompetence C ON J.Competence_ID = C.CompetenceID
WHERE C.CompetenceName IN ('drivers license', 'high school degree', 'black hair')
GROUP BY P.PersonID, P.SurName, P.LastName
HAVING COUNT(*) = 3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886