How to use WHERE statement in SQL to exclude a group

Question

I have a complex query, but here is the essence of what I'm trying to do

In a table looking like this:

Col1 Col2  
Bill 0  
Bill 0  
Bill 0  
Bill 1  
Bill 1  
John 1  
John 1  
Jonh 1

The above table has 2 columns. I am trying to query the rows that has '1' in all the rows in column 2. Since 'Bill' rows with '0' and '1', bill must be excluded. Subquery in the WHERE statement gives more than one result, and doen't work.

SELECT t1.Col1
FROM Table1 t1
WHERE t1.Col1 <> (SELECT t1.Col1 FROM Table1 t1 WHERE t1.Col2 <> '0')

Using a form of loop statement in the query would bring a whole new level of headache to my project so I hope any smart person can assist me in my quest.

Just to clarify, from the sample data above should the query return "John" or nothing? — GarethD, Mar 06 '12 at 13:19

score 4 · Answer 1 · edited May 23 '17 at 11:48

There are a number of approaches. Each one has its merits depending on your RDBMS and whatever you find easiest to read. See here for more details about performance and check your execution plans or do some testing to obtain the solution that best suits your needs.

SELECT  *
FROM    Table t1
WHERE   t1.Col1 NOT IN (SELECT t1.Col1 FROM Table1 t1 WHERE t1.Col2 <> '0')

or

SELECT  t1.*
FROM    Table t1
        LEFT JOIN
        (   SELECT  Col1
            FROM    Table
            WHERE   Col2 <> '0'
        ) Exc
            ON exc.Col1 = t1.Col1
WHERE   Exc.Col1 IS NULL

or

SELECT  *
FROM    Table t1
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    Table t2
            WHERE   t2.Col2 <> '0'
            AND     t1.Col1 = t2.Col1
        )

So substituting '<>' with 'NOT IN' makes all the difference? I'm eager to try that! — Sweetspot, Mar 06 '12 at 18:28

score 3 · Answer 2 · answered Mar 06 '12 at 12:13

3

SELECT t1.Col1 
FROM Table1 t1 
WHERE NOT EXISTS
(SELECT t2.Col1 
FROM Table1 t2
WHERE t2.col2 = 0
AND t2.col1 = t1.col1)

answered Mar 06 '12 at 12:13

Dibstar

2,334
2
24
38

Comparing the nested query to the main query? Nice! But will that use resources exponentially when used in more complicated queries? – Sweetspot Mar 06 '12 at 18:28
A good place to read up on the implications of exists is here: http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/ – Dibstar Mar 07 '12 at 08:33

score 1 · Answer 3 · answered Mar 06 '12 at 12:20

1

SELECT Col1
  FROM YourTable
 WHERE Col2 = 1
EXCEPT
SELECT Col1
  FROM YourTable
 WHERE Col2 <> 1;

answered Mar 06 '12 at 12:20

onedaywhen

55,269
12
100
138

no correct answer mark for this, it solved everything? – tcoulson May 08 '19 at 14:26

score 0 · Answer 4 · 2012-03-06T13:05:39.440

0

Try:

SELECT Col1 
FROM Table1
GROUP BY Col1
HAVING COUNT(DISTINCT Col2)=1 and MAX(Col2)='1'

edited Mar 06 '12 at 13:05

answered Mar 06 '12 at 12:28

I don't think this would work correctly, although the OP's query does not match his question so it is hard to say for definite. If there were another row e.g col1 = 'foo', col2 = 0. Your query would return both John and Foo, whereas based on the question it should only return John, and based on the query in the question it should only return Foo. I think you'd need `HAVING COUNT(CASE WHEN Col2 != '0' THEN 1 END) = 0` instead of `HAVING COUNT(DISTINCT Col2) = 1. Or you could use `HAVING COUNT(Col2) = COUNT(CASE WHEN Col2 = '0' THEN 1 END)` – GarethD Mar 06 '12 at 12:56
Oops - I missed the requirement to return only Col2='1' (instead of just any single value). Query updated accordingly. – Mar 06 '12 at 13:07
Not return Col2='1', but exclude Bill and only return John because some of Bill's Col2 has the value of '0'. – Sweetspot Mar 06 '12 at 18:28
@user1251900: From your question: "I am trying to query the rows that has '1' in all the rows in column 2." – Mar 07 '12 at 09:31

How to use WHERE statement in SQL to exclude a group

4 Answers4