T SQL Cross Table Query

Another T SQL question please!

I have a table QualificationProfile which is used to define which qualifications are required for a specific profile.  So for example it contains the columns

ID
PROFILE_ID
QUALIFICATION_ID

Where PROFILE_ID links to the PROFILE table and QUALIFICATION_ID links to a qualifications table.

I also have a PersonnelQualification table which links a person to their qualification using the following fields

ID
PERSON_ID
QUALIFICATION_ID

My Question is - How can I get a list of People who match a specific Profile? I.e. they have all of the qualifications defined by a specific profile?
ChrisMDAsked:
Who is Participating?
 
Brendt HessConnect With a Mentor Senior DBACommented:
SELECT
    pq.PERSON_ID
FROM (
    SELECT
        Min(IfNull(pq.person_id, -1))
    FROM bo.QualificationProfile qp
    LEFT OUTER JOIN dbo.PersonnelQualification pq
        ON pq.QUALIFICATION_ID = qp.QUALIFICATION_ID
    WHERE qp.QUALIFICATION_ID = <qualification_id>
    GROUP BY pq.PERSON_ID
    )
WHERE pq.PERSON_ID > 0
0
 
didnthaveanameCommented:
Can we get a list of all the tables involved, their relationships/constraints and some sample data (Mostly just want to see how personnel with multiple qualifications are handled)?
0
 
Manuel Marienne-DuchêneITMCommented:
SELECT a.person_id FROM PersonnelQualification a,qualificationProfile b
WHERE a.qualification_id = b.qualification_id
AND b.profile_id = ....
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
ChrisMDAuthor Commented:
PERSONNEL Table

ID
FIRSTNAME
LASTNAME

QUALIFICATION Table

ID
NAME

PROFILE Table

ID
NAME

PERSONNEL_QUALIFICATION Table

ID
PERSONNEL_ID          (FK to PERSONNEL table)
QUALIFICATION_ID   (FK to QUALIFICATION Table)

PROFILE_QUALIFICATION Table

ID
PROFILE_ID                  (FK To PROFILE Table)
QUALIFICATION_ID      (FK to QUALIFICATION table)

They key is that the PERSONNEL_QUALIFICATION  links a person to their qualifications and the PROFILE_QUALIFICATION links a Profile to the required qualifications.  So for a user to match a profile they must have ALL of the qualifications listed in the specified profile.

The problem is that a profile may have 1 to n qualifications which is unknown so that suggested answer above will not work as there could be 1 qualficiation in a profile or 20.
0
 
didnthaveanameCommented:
i would try:

declare @qualsCount int;

select @qualsCount = count( * ) from profile_qualification where profile_ID = <ID>;

select
   PEQ.personnel_ID
from
   profile_qualification as PRQ
      inner join personnel_qualification as PEQ on PRQ.qualification_ID = PEQ.qualification_ID
group by
   PEQ.personnel_ID
having
   PRQ.profile_id = <ID> and
   count( PEQ. qualification_ID ) = @qualsCount;

Open in new window


I think there is a more elegant way to do this with windowing, but I don't use windowing as often as I should.
0
 
ThomasianCommented:
SELECT	*
FROM
	PERSONNEL P
WHERE
	NOT EXISTS (
		SELECT	1
		FROM
			PROFILE_QUALIFICATION PRQ LEFT JOIN
			PERSONNEL_QUALIFICATION PEQ
				ON PRQ.QUALIFICATION_ID = PEQ.QUALIFICATION_ID
				AND P.PERSONNEL_ID = PEQ.PERSONNEL_ID
		WHERE
			PEQ.QUALIFICATION_ID IS NULL
			AND PRQ.PROFILE_ID = @PROFILE_ID
	)

Open in new window

0
 
Scott PletcherSenior DBACommented:
SELECT
    qp.PROFILE_ID, pq.PERSON_ID
FROM dbo.QualificationProfile qp
LEFT OUTER JOIN dbo.PersonnelQualification pq ON
    pq.QUALIFICATION_ID = qp.QUALIFICATION_ID
--WHERE qp.PROFILE_ID IN (...)
GROUP BY
    qp.PROFILE_ID, pq.PERSON_ID
HAVING
    COUNT(qp.PROFILE_ID) = COUNT(pq.PERSON_ID)
--ORDER BY ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.