October 28, 2014 at 12:21 pm
Hi,
I wonder if someone could help me with a bit of TSQL I'm struggling with (SQL 2008)...
I have 3 tables...
JobRequirements (A)
JobID int
QualificationTypeID int
EmployeeQualifications (B)
EmployeeID int
QualificationTypeID int
Employee (C)
EmployeeID int
EmployeeName int
I need to return a list of all employees fit for a specific job ... The criteria is that only employees who have all the JobRequirements are returned. So if a job had 3 requirements and the employee had just 2 of those qualifications, they would not be returned. Likewise, the employee might have more qualifications than the job requires, but unless the employee has all the specific qualifications the job requires they are not included. If an employee has all the job qualifications plus they have extra qualifications then they should be returned...
Can someone help me here I've been on this for hours with no luck. I'm thinking I might need a CTE...
Basically the bit I'm stuck on is how to only return those records where all the child records are present in the other table..
Many thanks.
October 28, 2014 at 12:53 pm
I think this would be a good place to make use of EXISTS.
For B, should there be an EmployeeID in there?
October 28, 2014 at 1:02 pm
Yes you are correct about table b, I've adjusted that now 🙂
Regards Exist, how would I use it to solve this issue?
Many thanks
Charlotte
October 28, 2014 at 2:04 pm
Charlottecb (10/28/2014)
Yes you are correct about table b, I've adjusted that now 🙂Regards Exist, how would I use it to solve this issue?
Many thanks
Charlotte
Relational division. Joe Celko and Dwain Camps have some excellent examples. Try googling pilots hangars and planes on this site. The coding might just be a milestone for you. If you get stuck, just post back (android phones don't yet run SQL Server :-))
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 28, 2014 at 2:35 pm
Hmm... I think I've finally got this one figured out (my initial thought of EXISTS would be been much messier!). Let's see:
-- First, find out how many qualifications
-- a given job has:
with Jobs as (
select JobID
, COUNT(QualificationTypeID) as NumQuals
from JobRequirements
group by JobID
),
EmployeeQuals as (
-- Now how many qualifications
-- each employee has per job:
select jr.JobID
, eq.EmployeeID
, COUNT(eq.QualificationTypeID) as NumQuals
from EmployeeQualifications eq
inner join JobRequirements jr
on eq.QualificationTypeID = jr.QualificationTypeID
group by jr.JobID, eq.EmployeeID
)
-- Now, give me the names of employees
-- where the employee has the same number
-- of qualifications as the job does
select e.EmployeeName
, eq.JobID
from Employees e
inner join EmployeeQuals eq
on e.EmployeeID = eq.EmployeeID
inner join Jobs j
on eq.JobID = j.JobID
and eq.NumQuals = j.NumQuals
)
October 28, 2014 at 3:04 pm
Hi SQLSlacker,
That's pretty similar to what I've come up with (and is working)...
;WITH
cteJobQualificationTypeIDs
AS
(
SELECT A.[QualificationTypeID]
FROM [OM_Master_Job_Sub_Type_Competency] A
WHERE A.[JobCompetencyID] IN (SELECT [splitstring] FROM udf_parse_string_into_integer_table(@JobCompetencyIDs)) -- JobSubTypeIDs --
),
cteEmployeeQualifications
AS
(
SELECT A.[LinkedToPersonID]
FROM GC_Person_Qualification A
JOIN cteJobQualificationTypeIDs B ON A.[QualificationTypeID] = B.[QualificationTypeID]
group by LinkedToPersonID
having COUNT(*) = @JobCompetencyCount
)
SELECT ISNULL(A.[intContractorID], 0) AS [ContractorID],
ISNULL(A.[strName], '') AS [ContractorName],
FROM [tblContractor] A
INNER JOIN cteEmployeeQualifications B ON A.[intContractorID] = B.[LinkedToPersonID]
I'll have a play with what you have posted to see how I get on with it.
Many thanks 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply