SELECT qualified employees based on program rrequirements

Question

SELECT qualified employees based on program rrequirements

jmced2334

SSC Veteran

Points: 292
More actions
July 31, 2015 at 1:49 pm

#320099

This is my first post so hopefully I'm doing it correctly. If not I apologize in advance. Basically I'm trying to produce a report that shows qualified employees for each program. Each employee can possess many qualifications. There will be no programID parameter submitted by the user. I just want to produce the report which shows the programs and the qualified employees for each. I thought I had a query that was working but once I added a different ProgramID into the ProgramModules table things went south.
Here are my tables:
tblEmployees (table of employees)
- EmployeeID
- EmployeeName
tblQualifications (table of qualifications)
- Qualification_ID
- QualificationTitle
tblEmployeeQualification (table of all employees qualifications)
-EmpQualificationID
-EmployeeID (fk for tblEmployees)
-QualificationID (fk for tblQualifications)
tblPrograms (table of programs)
-ProgramID
-ProgramTitle
tblProgramModules (table of qualifications required by each program)
-ProgramModuleID
-ProgramID (fk for tblPrograms)
-QualificationID (fk for tblQualifications)
Here is the query I was working with that works when there are only records in the ProgramModules table that use the same ProgramID
SELECT
tblProgramModules.TrainingProgramID,
tblEmployees.EmployeeID,
tblEmployees.EmployeeName
FROM
tblEmployees
INNER JOIN tblEmployeeQualifications ON (tblEmployeeQualifications.EmployeeID = tblEmployees.EmployeeID)
INNER JOIN tblProgramModules ON (tblProgramModules.QualificationID = tblEmployeeQualifications.QualificationID)
GROUP BY
tblProgramModules.ProgramID,
tblEmployeeQualifications.EmployeeID,
tblEmployees.EmployeeID,
tblEmployees.EmployeeName
HAVING
COUNT(*) = (SELECT COUNT(*) FROM tblProgramModules)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Could you please post the CREATE TABLE statements for each of the listed tables, as well as same sample data in the form of INSERT statements, so that we can easily test queries?

Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 100951 More actions · Answer 2

SELECT pm_emp.ProgramID, pm_emp.EmployeeID, e.EmployeeName

FROM (

SELECT

pm.ProgramID,

eq.EmployeeID

FROM tblProgramModules pm

LEFT OUTER JOIN tblEmployeeQualifications eq ON (eq.QualificationID = pm.QualificationID)

GROUP BY

pm.ProgramID,

eq.EmployeeID

HAVING

COUNT(pm.QualificationID) = COUNT(eq.QualificationID)

) AS pm_emp

INNER JOIN tblEmployees e ON e.EmployeeID = pm_emp.EmployeeID

FYI, actual CREATE TABLE statements would have been much more useful than plain text.

Get rid of the identity columns in the intersection tables, i.e., tblEmployeeQualification and tblProgramModules.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

In addition to the good feedback you've already gotten from Gail and Scott, I figured I'd try to explain the probable cause of the issue you're seeing.

In your code, the SELECT COUNT(*) subquery is just returning the number of rows in tblProgramModules, and the HAVING clause says to only return those program/employee combinations that have exactly that many rows in the result set. So long as the joins happen to yield a result set where each program/employee combination is duplicated the same number of times as there are rows in tblProgramModules, you'll see the rows. The moment that changes, you won't.

Are you just using the GROUP BY to return distinct rows?

Cheers!

jmced2334 SSC Veteran Points: 292 More actions · Answer 4

Sorry guys I should have given more details. Hopefully this post will provide better information. Ignore the table names lists above in my op. The whole point of this is to easily identify employees who are qualified to work in certain areas. Each area can have different types of required training needed to work in that area.

In case the tablenames are a little confusing here is a summary

tblEmployees- Contains all the employees

tblTraining- Contains all the potential types of training an employee can receive

tblEmployeeTraining - Contains all the types of training an employee actually has

tblTrainingPrograms- Contains all the different areas an employee can work in

tblTrainingProgramModules - Contains all the different types of training an area requires.

Here are some create scripts as well as some insert data. Given the data below

Floor 1 Area Cleaning should only return Jim

Floor 2 Area Cleaning should return no one

Floor 3 Area Cleaning should return only Jim again

CREATE TABLE [dbo].[tblEmployees] (

[EmployeeID] int NOT NULL PRIMARY KEY,

[FirstName] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,

[LastName] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL

)

ON [PRIMARY]

GO

INSERT INTO [tblEmployees]

([EmployeeID],[FirstName],[LastName])

VALUES(1111111,'Jim','Smith')

GO

INSERT INTO [tblEmployees]

([EmployeeID],[FirstName],[LastName])

VALUES(2222222,'Mary','Smith')

GO

INSERT INTO [tblEmployees]

([EmployeeID],[FirstName],[LastName])

VALUES(3333333,'Joe','Brown')

GO

COMMIT

GO

CREATE TABLE [dbo].[tblTraining] (

[Training_ID] int IDENTITY(1, 1) NOT NULL PRIMARY KEY,

[TrainingDescription] varchar(100) COLLATE Latin1_General_CI_AS NOT NULL

)

ON [PRIMARY]

GO

INSERT INTO [tblTraining]

([Training_ID],[TrainingDescription])

VALUES(1,'Room Cleaning')

GO

INSERT INTO [tblTraining]

([Training_ID],[TrainingDescription])

VALUES(2,'Mechanical Room Cleaning')

GO

INSERT INTO [tblTraining]

([Training_ID],[TrainingDescription])

VALUES(3,'Common Area Cleaning')

GO

COMMIT

GO

CREATE TABLE [dbo].[tblEmployeeTraining] (

[EmpTraining_ID] int IDENTITY(1, 1) NOT NULL PRIMARY KEY,

[EmployeeID] int NOT NULL,

[TrainingID] int NOT NULL

)

ON [PRIMARY]

GO

INSERT INTO [tblEmployeeTraining]

([EmpTraining_ID],[EmployeeID],[TrainingID])

VALUES(1,1111111,1)

GO

INSERT INTO [tblEmployeeTraining]

([EmpTraining_ID],[EmployeeID],[TrainingID])

VALUES(2,1111111,2)

GO

INSERT INTO [tblEmployeeTraining]

([EmpTraining_ID],[EmployeeID],[TrainingID])

VALUES(3,2222222,2)

GO

INSERT INTO [tblEmployeeTraining]

([EmpTraining_ID],[EmployeeID],[TrainingID])

VALUES(4,3333333,3)

GO

COMMIT

GO

CREATE TABLE [dbo].[tblTrainingPrograms] (

[TrainingProgram_ID] int IDENTITY(1, 1) NOT NULL PRIMARY KEY,

[ProgramDescription] varchar(100) COLLATE Latin1_General_CI_AS NOT NULL

)

ON [PRIMARY]

GO

INSERT INTO [tblTrainingPrograms]

([TrainingProgram_ID],[ProgramDescription])

VALUES(1,'Floor 1 Area Cleaning')

GO

INSERT INTO [tblTrainingPrograms]

([TrainingProgram_ID],[ProgramDescription])

VALUES(2,'Floor 2 Area Cleaning')

GO

INSERT INTO [tblTrainingPrograms]

([TrainingProgram_ID],[ProgramDescription])

VALUES(3,'Floor 3 Area Cleaning')

GO

COMMIT

GO

CREATE TABLE [dbo].[tblTrainingProgramModules] (

[TrainingModule_ID] int IDENTITY(1, 1) NOT NULL PRIMARY KEY,

[TrainingProgramID] int NOT NULL,

[TrainingID] int NOT NULL

)

ON [PRIMARY]

GO