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:
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')
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)