0

Using this solution, I learned how to nest a query but I'm finding that the WHERE statement is ignored?

I want to return only EmpID that have the 4 QualID specified found in the Quantifications tables. It appears this returns EmpID where any of these values match the EmpID.

Employee Table: EmpID, EmpName

Qualifications Table: EmpID, QualID

SELECT EmpID, QualID
FROM
(
   SELECT Employee.EmpID as EmpID, HTG_TechQual.QualID as QualID 
   FROM Employee Employee 
   INNER JOIN Qualifications Qualifications ON Qualifications.EmpID = Employee.EmpID
   WHERE Qualifications.QualID IN ('3', '34', '35', '37')
 ) X
GROUP BY EmpID DESC
Community
  • 1
  • 1
Rocco The Taco
  • 3,695
  • 13
  • 46
  • 79

1 Answers1

1

Use a HAVING clause to select only those groups having all 4 QualIDs 

SELECT e.EmpID
FROM Employee e 
INNER JOIN Qualifications q ON q.EmpID = e.EmpID
WHERE q.QualID IN ('3', '34', '35', '37')
GROUP BY e.EmpID
HAVING count(distinct q.QualID) = 4
juergen d
  • 201,996
  • 37
  • 293
  • 362