Find list of values that match all values in list

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Find list of values that match all values in list

Author

Topic

shaft01
Starting Member

7 Posts

Posted - 2010-05-10 : 09:10:08

Hi All,

The situation I'm in is I've got a table with employees, a table with employee qualifications linked by employeeID and I want to write a query that will bring me all employees that have got all the qualifications in a comma separated list that has been passed to the stored procedure and converted to a table using a table-valued function.

The only way that I can think of doing it at the moment is to write a function that I pass the list of qualifications and the employeeID to and it would return true or false using the ALL comparison function. This seems to complicated though I sure there must be an easier way of doing it.

Please help it's doing my head in.

Kristen
Test

22859 Posts

Posted - 2010-05-10 : 09:20:57

[code]
SELECT ...
FROM MyEmployeesTable AS E
JOIN MyQualificationsTable AS Q
ON Q.EmployeeID = E.EmployeeID
JOIN dbo.MySplitFunction(@QualificationsListAsCSV) AS QL
ON QL.QualificationID = Q.QualificationID
[/code]

Kristen
Test

22859 Posts

Posted - 2010-05-10 : 09:22:57

Or if the Qualification List has already been split into an @TableVar then:


SELECT ...
FROM MyEmployeesTable AS E
     JOIN MyQualificationsTable AS Q
          ON Q.EmployeeID = E.EmployeeID
     JOIN @QualificationsListTable AS QL
          ON QL.QualificationID = Q.QualificationID

shaft01
Starting Member

7 Posts

Posted - 2010-05-10 : 09:38:05

HI Kirsten,

that will bring back employees that have any of the qualifications in the list, I only want employees that have got all the qualifications.

Kristen
Test

22859 Posts

Posted - 2010-05-10 : 09:50:55

Ah, sorry. Mis-read the question

This perhaps?


SELECT E.EmployeeID
FROM MyEmployeesTable AS E
     JOIN MyQualificationsTable AS Q
          ON Q.EmployeeID = E.EmployeeID
     JOIN @QualificationsListTable AS QL
          ON QL.QualificationID = Q.QualificationID
GROUP BY E.EmployeeID
HAVING COUNT(*) = (SELECT COUNT(*) FROM @QualificationsListTable)

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-10 : 10:35:05

Another way (which I personally prefer):


SELECT E.EmployeeID, E.Name,...
  FROM Employees AS E
 WHERE NOT EXISTS(SELECT *
                    FROM @RequiredQualifications AS RQ
                   WHERE NOT EXISTS(SELECT *
                                      FROM EmployeeQualifications AS EQ
                                     WHERE EQ.EmployeeID = E.EmployeeID
                                       AND EQ.QualificationID = RQ.QualificationID));

shaft01
Starting Member

7 Posts

Posted - 2010-05-10 : 11:24:39

That works like a dream cheers Mal the trouble is I can't get my head around whats actually happening, and I like to understand what I'm doing. Could you explain in laymans terms what is happening to get the results.

Cheers

Kristen
Test

22859 Posts

Posted - 2010-05-10 : 11:55:42

Mine:

Join Employee to Qualifications and CSV data. Each employee (selected) will have 1, or more, rows selected.

Those employees HAVING the same number selected as matches the number of values in the CSV data table match ALL (rather than just Some) of the Qualifications.

malpashaa can explain his himself! I understand it, but personally I would avoid the double-negative in my code as it would need some considerable thought each time I had to modify the code in/around it.

shaft01
Starting Member

7 Posts

Posted - 2010-05-11 : 06:33:28

Hi Kirsten,

The problem with your solution it is not a match against the actual qualifications what happens if there are duplicates, with mal's it's an exact match against the list.

Cheers
James

Kristen
Test

22859 Posts

Posted - 2010-05-11 : 06:48:11

You mean the same qualification might be in the CSV list twice?

If so I did ponder that and thought that both routes would work for that.

However, if the EmployeeQualifications table has duplicate values for EmployeeID + QualificationID then it definitely won't work - but I'd be worried about referential integrity if there were DUPs like that in the database.

If I've misunderstood an example would help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources