CODEWITHSUNDEEP
sqloracle-database
user3007002
user3007002

Reputation: 25

SQL Where Clause Help Needed

    A   B   C
1   bob 55  0
2   bob 55  1

I need help with my where clause. The example above shows Bob has two records. I only want to show records if all of Bob's records have a value of 1 in column C. If one of Bob's records has a value of 0 then it should return nothing for Bob. Currently I'm only able to get it to return the one record that has a value of 1.

Existing query

select a, b, c
from table
where b = 55 and c = 1

Upvotes: 1

Views: 64

Answers (4)

datadevelopr
datadevelopr

Reputation: 53

I would suggest slightly modified SQL (included the WHERE clause spec'd by Gordon):

select t.a, t.b, t.c
from table t
where t.b = 55 and t.c = 1 and not exists (select 1
                  from t t2
                  where t2.a = t.a and t2.b = t.b and t2.c = 0
                 );

Massimino's solution also works, assuming column c only ever has values of 1 or 2.

Upvotes: 1

JeffO
JeffO

Reputation: 8043

You can create a sub query that finds any records equal to one. If that occurs, you would not include them.

    select a, b, c
    from table as t
    where not exists (
       select * 
       from table as t1 
       where t.a = t1.a and IsNull(t1.c, -1) <> 1
    )

This way, it won't matter what variety of values for column c could occur, they'll have a record even if you get some nulls (Just in case you're not familiar with your data.). The sub query inside the parenthesis can be run by itself to test or see if you need other criteria.

You could join this table to itself and accomplish something similar, but I think this code example shows intent.

Upvotes: 0

Massimo Petrus
Massimo Petrus

Reputation: 1891

Try using NOT EXISTS as follows

  select a, b, c
  from table t
  where t.b = 55 and not exists (select 1 from table t2 
  where t2.b=t.b and t2.c=0)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

The simplest way is probably to use not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.a = t.a and t2.c <> 1
                 );

Upvotes: 1

Related Questions