CODEWITHSUNDEEP
sqlsql-server
jamheadart
jamheadart

Reputation: 5323

Exclude from output if column has any entry other than xyz

I'm trying to output rows where Zipcodes have entries of ONLY category a - e.g. on the table below:

Id | Category | Name | ZipCode | Extraneous
--------------------------------------------
1      a         Jim    1234      a comment
2      a         Jim    1234      other
3      b         Jim    1234      no way
4      a         Baz    5678      foobar
5      c         Gil    0000      jiminy
6      a         Amy    0000      wibble

I should only be getting row 4. I don't want zipcode 1234 to show even if it has an a entry, because it also has an entry for category b - similar with Zipcode 0000 has an entry for c so shouldn't be shown.

So far I'm doing:

select max(t1.Id),
    t1.Category,
    t1.Name,
    t1.ZipCode,
    t1.Extraneous
from tbl t1
join tbl t2 on t1.Zipcode = t2.Zipcode
where t1.Category = 'a'
and t2.Category != 'b' and t2.Category != 'c'
group by
    t1.Category,
    t1.Name,
    t1.ZipCode,
    t1.Extraneous

Which still gives results for ZipCode 1234

How do I get all instances of a ZipCode which have entries for b but only if they have no other category entries?

Upvotes: 0

Views: 122

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

You can simply use not exists:

select t.*
from tbl t
where not exists (select 1
                  from tbl t2
                  where t2.zipcode = t.zipcode and t2.category <> 'A'
                 );

You can also use window functions. For instance:

select t.*
from (select t.*,
            sum(case when category = 'a' then 0 else 1 end)
            over (partition by zipcode) as cnt_nota
     from tbl t
    ) t
where cnt_nota = 0;

Upvotes: 1

Isolated
Isolated

Reputation: 6454

Can use except..

    select zipcode
    from table1
    where category = 'a'
    except
    select zipcode
    from table1
    where category <> 'a'

Upvotes: 0

Andrew
Andrew

Reputation: 8758

Using not exists:

select
a.*

from
table1 a
where not exists (select zipcode from table1 b where a.zipcode = b.zipcode and b.category <> 'A')
and a.category = 'A'

Upvotes: 2

Related Questions