Solved

T SQL Cross Table Query

Posted on 2013-06-10
7
325 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

739 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