Solved

T SQL Cross Table Query

Posted on 2013-06-10
7
318 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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:ScottPletcher
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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. …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now