rawe
03-15-2007, 05:00 PM
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
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