Search code examples
sql-serverrowfilter

How to filter a table against a filtered table

I have a code table containing a list of sports code. And I have another table containing a list of staff that could contain multiple sports code.

I want to return only the staff whose sports code can cover that in the code table.

Like this:

sample

In this case Jason have every code in the code table and Jackson is one code short (codeID 3) So only Jason is return.

Declare @code table
              (
                  codeID varchar(4),
                  codeDes varchar(72)
                  Primary Key(codeID, codeDes)
              )

Insert into @code
Values ('1', 'apple picking'), ('2', 'pear picking'), ('3', 'farming')

Declare @staff table
               (
                   staffID int,
                   name varchar(8),
                   codeID varchar(4)
               )

Insert into @staff
Values (1, 'Jason', '1'), (1, 'Jason', '2'), 
       (1, 'Jason', '3'), (1, 'Jason', '4'), 
       (2, 'Jackson', '1'), (2, 'Jackson', '2')
Solution

  • You can try below sample assumption staff holds unique code.

    SELECT staffId, Name
FROM @staff  s
INNER JOIN @code c ON c.codeID = s.codeID
GROUP BY StaffId, Name
HAVING COUNT(DISTINCT s.CodeId) = (SELECT COUNT(1) FROM @code)