Q:

Return records in SQL that include all values from IN clause

Apologies if the title is not completely accurate, but I am not sure how to phrase my question exactly. I would like to return only rows where the results include all items in the IN statement grouped on particular columns. So my query is:
    SELECT [DRSY],
           [DRRT],
           [DRKY],
           [DRDL01]
      FROM dbo.f0005
     WHERE DRKY IN ('FC', 'OO', 'SH')

I want to return records where DRSY and DRRT include all of the items 'FC', 'OO', 'SH'. Perhaps a picture will help illustrate this:

The only rows I want returned are where DRSY = '00' and DRRT = 'DT' because this includes all 3 values I specified. I have tried searching for different ways to use IN, EXISTS, and even ALL. 

A:

I believe this can be done by GROUPING and then ensuring the DISTINCT count is equal to the number of search items:
   SELECT [DRSY],
          [DRRT]
   FROM dbo.f0005
   WHERE DRKY IN ('FC', 'OO', 'SH')
   GROUP BY [DRSY], [DRRT]
   HAVING COUNT(DISTINCT DRKY) = 3;

Edit, Re Ensuring Count of Items remains in Sync
What you could do is build up a derived table or CTE containing the list of desired values (searchValues), which you can then join to (instead of IN) and then you will be able to COUNT the searchValues to avodi any maintenance issues with counting values.
WITH searchValues AS
(
    select val 
    from (values ('FC'), ('OO'), ('SH')) as s(val)
)
SELECT [DRSY],
       [DRRT]
   FROM dbo.f0005
   INNER JOIN searchValues s
   ON DRKY = s.val
   GROUP BY [DRSY], [DRRT]
   HAVING COUNT(DISTINCT DRKY) = (SELECT COUNT(val) FROM searchValues);

SqlFiddle here

