July 31, 2015 at 1:49 pm
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)
July 31, 2015 at 2:15 pm
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
July 31, 2015 at 2:17 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 31, 2015 at 2:38 pm
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!
July 31, 2015 at 3:26 pm
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
INSERT INTO [tblTrainingProgramModules]
([TrainingModule_ID],[TrainingProgramID],[TrainingID])
VALUES(1,1,1)
GO
INSERT INTO [tblTrainingProgramModules]
([TrainingModule_ID],[TrainingProgramID],[TrainingID])
VALUES(2,1,2)
GO
INSERT INTO [tblTrainingProgramModules]
([TrainingModule_ID],[TrainingProgramID],[TrainingID])
VALUES(3,2,2)
GO
INSERT INTO [tblTrainingProgramModules]
([TrainingModule_ID],[TrainingProgramID],[TrainingID])
VALUES(4,2,3)
GO
INSERT INTO [tblTrainingProgramModules]
([TrainingModule_ID],[TrainingProgramID],[TrainingID])
VALUES(5,3,1)
GO
COMMIT
GO
July 31, 2015 at 4:47 pm
Main SQL query in the bottom code box. But, I must stress again, to maintain data integrity and drastically help performance, you must:
GET RID OF THE DOPEY AND DANGEROUS IDENTITY COLUMNS ON THE INTERSECTION TABLES!!
CREATE TABLE [dbo].[tblEmployeeTraining] (
--[EmpTraining_ID] int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
[EmployeeID] int NOT NULL,
[TrainingID] int NOT NULL,
CONSTRAINT tblEmployeeTraining__PK PRIMARY KEY ( TrainingID, EmployeeID )
)
CREATE TABLE [dbo].[tblTrainingProgramModules] (
--[TrainingModule_ID] int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
[TrainingProgramID] int NOT NULL,
[TrainingID] int NOT NULL,
CONSTRAINT tblTrainingProgramModules__PK PRIMARY KEY ( [TrainingProgramID], [TrainingID] )
)
SELECT tpm_emp.TrainingProgramID, tpm_emp.EmployeeID, e.FirstName, e.LastName
FROM (
SELECT
tpm.TrainingProgramID,
et.EmployeeID
FROM tblTrainingProgramModules tpm
LEFT OUTER JOIN tblEmployeeTraining et ON et.TrainingID = tpm.TrainingID
GROUP BY
tpm.TrainingProgramID,
et.EmployeeID
HAVING
COUNT(et.TrainingID) =
(SELECT COUNT(*) FROM tblTrainingProgramModules tpm2 WHERE tpm2.TrainingProgramID = tpm.TrainingProgramID)
) AS tpm_emp
INNER JOIN tblEmployees e ON e.EmployeeID = tpm_emp.EmployeeID
INNER JOIN tblTrainingPrograms tp ON tp.TrainingProgram_ID = tpm_emp.TrainingProgramID
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 31, 2015 at 4:56 pm
Thanks for the quick response and feedback. I'll give that query a try. Thanks again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply