CODEWITHSUNDEEP
sqlsql-server
Bcpouli
Bcpouli

Reputation: 225

How to exclude a group based on one element of another column?

I have a dataset that looks like this:

    Claim   Type
    0       1
    107444  2
    107444  5
    107445  2
    107446  2

etc...

as a result of this simple query:

    select Claim,[Type] from myTbl
    Group by Claim,[Type]
    order by Claim

I want to write a query that completely excludes Claims that have a Type = 5, so the result of this would be:

    Claim   Type
    0       1
    107445  2
    107446  2

Any ideas on how to accomplish this?

Upvotes: 0

Views: 87

Answers (2)

Hugo Hilário
Hugo Hilário

Reputation: 2908

You want to use the HAVING clause.

http://www.w3schools.com/sql/sql_having.asp

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269613

You can do this with logic in the where clause:

select Claim, [Type]
from myTbl
where claim not in (select Claim from myTbl where [Type] = 5)

Upvotes: 1

Related Questions