MR04 MR04 - 18 days ago 5
SQL Question

How do I retrieve a list of employees that have 5 specific traits?

I have a table

EmpTraits
with a list of employees and their traits. So this table will list each employee and all of their traits (employee can be listed multiple times as long as the trait is different, so this table doesn't have 2 records with same employee and same trait).

`Table: EmpTraits
+------------+---------+
| EmployeeID | TraitID |
+------------+---------+`


Let's say I have a VIEW called
EmpXTraits
(Employee X Traits) which just lists all the traits Employee X has (which is a total of 5).

`Table: EmpXTraits
+------------+---------+
| EmployeeID | TraitID |
+------------+---------+
| X + 1 +
| X + 2 +
| X + 3 +
| X + 4 +
| X + 5 +
+------------+---------+`


From these two tables, I'm trying to query a list of all EmployeeIDs that have all of the traits shown in the VIEW
EmpXTraits
.

I have the following query so far, but the problem is that is shows employees that have the exact same traits as employee X. So if another employee has all those similar traits and more, he would not show up.

`
SELECT DISTINCT EmpTraits.EmployeeID
FROM EmpTraits INNER JOIN EmpXTraits ON
EmpTraits.EmployeeID = EmpXTraits.EmployeeID
WHERE EmpTraits.TraitID IN (
SELECT EmployeeID
FROM EmpXTraits
);`


Any ideas how I can change this query to show all employees who have at least the traits from employee X (they can have more traits as long as they have all traits that employee X has).

Thank you!!

Answer

Please try the following. (No need to know before hand how many traits are in EmpXTraits)

SELECT DISTINCT ee.EmployeeID
  FROM EmpTraits AS ee
 WHERE NOT EXISTS (SELECT x.TraitID FROM EmpXTraits AS x 
                   EXCEPT 
                   SELECT e.TraitID FROM EmpTraits AS e 
                    WHERE e.EmployeeID = ee.EmployeeID);