CODEWITHSUNDEEP
sqlsql-server-2008-r2
skjcyber
skjcyber

Reputation: 5957

Multiple conditions in having clause

I have a table like below (using SQL server 2008 R2):

CREATE TABLE [dbo].[Data](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [IsBad] [bit] NOT NULL
) ON [PRIMARY]
GO

Insert into Data  values(100,'Book!',1)
Insert into Data  values(100,'Booklki**',1)
Insert into Data  values(100,'Book',0)
Insert into Data  values(100,'New Book ~~',1)
Insert into Data  values(100,'New Book',0)
Insert into Data  values(100,'B00k…>',1)
Insert into Data  values(101,'Tim3#',1)
Insert into Data  values(101,'%Timer%',1)
Insert into Data  values(101,'T1mer**',1)
Insert into Data  values(101,'Tim6',1)
Insert into Data  values(101,'Time@me',1)
Insert into Data  values(102,'ABC',0)
Insert into Data  values(102,'CDE',0)

I need to select all the ID which are having all IsBad = 1. So, querying above table will return ID: 101 . It must not return 102 or 100 because these IDs are having at least one IsBad=0.

I tried below query

select id,count(distinct isBad) as Total
from Data
group by id
having count(distinct isBad)= 1

This query includes the IDs which are having all IsBad=0. but I dont need that. I tried to add some more conditions in having clause using AND , but getting error.

How to proceed ? Any help is appreciated.

EDIT: I need to run the query against a table having 50 Million records. So, the query needs to be optimized to return the result in less time.

Upvotes: 0

Views: 7743

Answers (3)

dave
dave

Reputation: 1550

The slowness of the current answer is likely due to the use of where not exists clause. I typically get around this peformance issue by using a left join and checking for the absence of a match.

select *
from Data d1
left join (select * from Data where IsBad = 0) d2
  on d1.id = d2.id
where d2.id is null

This is an old post so it probably will not help the original individual but perhaps others will benefit.

Upvotes: 1

Mark Brackett
Mark Brackett

Reputation: 85655

Inverse it - you want "all the ID which have all IsBad = 1", which means the ID can't have any IsBad = 0:

SELECT ID FROM Data WHERE ID NOT IN (
    SELECT ID FROM Data WHERE IsBad = 0
)

Upvotes: 2

Andomar
Andomar

Reputation: 238076

select  *
from    Data d1
where   not exists 
        (
        select  *
        from    Data d2
        where   d1.id = d2.id
                and d2.IsBad = 0
        )

Live example at SQL Fiddle.

If you're just looking for the id, you can use:

select  distinct id
... rest of the query is the same ...

Upvotes: 2

Related Questions