?
Solved

T SQL Cross Table Query

Posted on 2013-06-10
7
Medium Priority
?
334 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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:
Brendt Hess earned 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

764 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