group by column not having specific value

Question

I am trying to obtain a list of Case_Id's where the case does not contain a specific RoleId using Microsoft Sql Server 2012.

For example, I would like to obtain a collection of Case_Id's that do not contain a RoleId of 4.

So from the data set below the query would exclude Case_Id's 49, 50, and 53.

    Id      RoleId  Person_Id   Case_Id
   --------------------------------------
    108     4       108         49
    109     1       109         49
    110     4       110         50
    111     1       111         50
    112     1       112         51
    113     2       113         52
    114     1       114         52
    115     7       115         53
    116     4       116         53
    117     3       117         53

So far I have tried the following

SELECT Case_Id
FROM [dbo].[caseRole] cr
WHERE cr.RoleId!=4
GROUP BY Case_Id ORDER BY Case_Id

Answer 1

The not exists operator seems to fit your need exactly:

SELECT DISTINCT Case_Id
FROM   [dbo].[caseRole] cr
WHERE  NOT EXISTS (SELECT *
                   FROM   [dbo].[caseRole] cr_inner
                   WHERE  cr_inner.Case_Id = cr.case_id 
                          AND cr_inner.RoleId = 4);

Answer 2

Just add a having clause instead of where:

SELECT Case_Id
FROM [dbo].[caseRole] cr
GROUP BY Case_Id
HAVING SUM(case when cr.RoleId = 4 then 1 else 0 end) = 0
ORDER BY Case_Id;