Querying multiple records in two tables

[rmorrison]

Hi, in my database I have tables for users (UserID, UserName),
projects (ProjectID, Project Name), and qualifications (QualID,
QualName). I have join tables for users_qualifications (UserID,
QualID), and projects_qualifications. (ProjectID, QualID).

What I need to do is run a query for a project to show which users
have the exact matching qualificiations.

Users can have many qualifications, projects can require many
qualifications, users may only work on a project if the
qualifications
required/held match exactly.


Please help.

 

[Dirk Goldgar]

Hi, in my database I have tables for users (UserID, UserName),
projects (ProjectID, Project Name), and qualifications (QualID,
QualName). I have join tables for users_qualifications (UserID,
QualID), and projects_qualifications. (ProjectID, QualID).

What I need to do is run a query for a project to show which users
have the exact matching qualificiations.

Users can have many qualifications, projects can require many
qualifications, users may only work on a project if the
qualifications
required/held match exactly.

When you say "match exactly", do you mean the user can't be over-qualified?
Normally, this sort of question is directed to finding users who possess *at
least* the required qualifications, not *exactly* those qualifications.

If you're satisfied with finding users who have at least the required
qualifications, but may be over-qualified, a query like this should do it, I
think:

SELECT
Projects.ProjectID,
Projects.ProjectName,
Users.UserID,
Users.UserName
FROM Projects, Users
WHERE
((((SELECT Count(*) FROM UsersQualifications UQ WHERE UQ.UserID =
Users.UserID AND UQ.QualID In (SELECT QualID FROM ProjectsQualifications PQ
WHERE PQ.ProjectID = Projects.ProjectID)))=(SELECT Count(*) FROM
ProjectsQualifications Q WHERE Q.ProjectID = Projects.ProjectID)));

Pardon the ugly line wrapping; I couldn't find a way to format that
complicated subquery expression prettily.

That query should (unless I'm mistaken) return the users who are qualified
for each project. But I haven't tested it with more than a tiny amount of
sample data.