Select Rows which does not satisfiy Predicate in SQL Server

Question

I have a table with 2 columns GroupID and Value. Column GroupID have two groups 1 and 2 and its respective values. I have to select GroupID which do not have Value 4

GroupID Value
1            4
1            5
1            6
1            7
1            8
2            5
2            7
2            8
2            9
2            3

OutPut:

GroupID   Value
2         5
2         7
2         8
2         9
2         3

Possible duplicate of [SQL: How do you select only groups that do not contain a certain value?](https://stackoverflow.com/questions/9355241/sql-how-do-you-select-only-groups-that-do-not-contain-a-certain-value) — Tab Alleman, Sep 14 '18 at 17:06

score 0 · Answer 1 · answered Sep 14 '18 at 16:35

You can try to use not exists

SELECT t1.* 
FROM T t1
WHERE not exists (
    SELECT 1 
    FROM T tt
    where tt.Value = 4 and tt.GroupID = t1.GroupID
)

another way you can use window function with count

select * from (
    SELECT t1.*,
           COUNT(CASE WHEN Value = 4 THEN 1 END) OVER(PARTITION BY GroupID ORDER BY GroupID) cnt
    FROM T t1
) t1
where cnt = 0

sqlfiddle

score 0 · Answer 2 · answered Sep 14 '18 at 16:35

0

You could use NOT IN

SELECT *
FROM YourTable
WHERE GroupID NOT IN (SELECT GroupID FROM YourTable WHERE Value = 4)

answered Sep 14 '18 at 16:35

SQLChao

7,709
1
17
32

score 0 · Answer 3 · answered Sep 14 '18 at 16:35

0

You can use not exists :

select t.* 
from table t
where not exists (select 1 
                  from table t1 
                  where t1.GroupID = t.GroupID and t1.value = 4
                 );

answered Sep 14 '18 at 16:35

Yogesh Sharma

49,870
5
26
52

score 0 · Answer 4 · answered Sep 14 '18 at 16:37

You can make use of having clause,

The HAVING clause sets conditions on the GROUP BY clause similar to the way that WHERE interacts with SELECT.

For your case the query would be-

Select groupID, value from tablename group by groupID having value !=4

Select Rows which does not satisfiy Predicate in SQL Server

4 Answers4