CODEWITHSUNDEEP
sql
Nate Pet
Nate Pet

Reputation: 46222

SQL Query to find which group does not have a given value

I am using T-SQL.

Say if I have the following

Value     Nbr
-----     ---
one       6
one       7
one       8
two       6
two       7
three     5
three     3
three     2

In the above table, I need to find which group does not have 6 in it. In this case, it is three as it does not have 6 in it.

What would be the best approach to do this?

I tried:

      select Value from tbl1 
      where nbr <> 6
      group by Value

but did not get the intended result.

Upvotes: 2

Views: 773

Answers (1)

user1172023
user1172023

Reputation: 

select distinct value
from tbl1
where value not in
(
    select distinct value
    from tbl1
    where nbr = 6
)

Upvotes: 5

Related Questions