Solved

T SQL Cross Table Query

Posted on 2013-06-10
7
322 Views
Last Modified: 2013-07-10
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?
0
Comment
Question by:ChrisMD
7 Comments
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39234851
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
 
LVL 6

Expert Comment

by:Manuel Marienne-Duchêne
ID: 39234859
SELECT a.person_id FROM PersonnelQualification a,qualificationProfile b
WHERE a.qualification_id = b.qualification_id
AND b.profile_id = ....
0
 

Author Comment

by:ChrisMD
ID: 39234864
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39234932
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
 
LVL 22

Expert Comment

by:Thomasian
ID: 39234974
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39235595
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
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 39235903
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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question