CODEWITHSUNDEEP
sqlt-sqlgroup-by
maxp
maxp

Reputation: 25161

Selecting rows in SQL only where *all* values do not meet a certain criteria

Given a really simple table in MS SQL like this:

User ID | Status
----------------
1         False
2         True
2         False
3         False
3         False

I'm having trouble wrapping my head around how I would select only users that don't have any rows with a Status assigned to True.

The result would return User IDs 1 and 3.

I have a feeling it's requires more than a straight forward WHERE selector, and have been experimenting with GROUP BY and COUNT without success.

Upvotes: 3

Views: 5527

Answers (4)

paparazzo
paparazzo

Reputation: 45096

select t1.[user id]
  from table1 t1
  left join table1 t2
    on t2.[user id] = t2.[user id] 
   and t2.[Status] = 'true'
 where t2.[user id] is null 
 group by t1.[user id]


select distinct [user id]
  from table1 
except 
select [user id]
  from table1 
 where [Status] = 'true'

Upvotes: 0

Taryn
Taryn

Reputation: 247820

You can use a GROUP BY with a HAVING clause to get the result. In your HAVING you can use a CASE expression with an aggregate to filter out any rows that have a Status = 'True':

select [user id]
from table1
group by [user id]
having sum(case when status = 'true' then 1 else 0 end) < 1;

See SQL Fiddle with Demo

Upvotes: 7

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

This should work. Please try

DECLARE @table TABLE (UserID INT,
                  Status VARCHAR(10))
INSERT INTO @table
VALUES
('1', 'False'),
('2', 'True'),
('2', 'False'),
('3', 'False'),
('3', 'False')

SELECT DISTINCT UserID,
             Status
FROM @table AS t1
WHERE EXISTS (SELECT 1
            FROM @table AS t2
            WHERE t1.UserID = t2.UserID
            GROUP BY UserID
            HAVING SUM(CASE
                       WHEN Status = 'True' THEN 1
                       ELSE 0
                    END ) = 0)

Upvotes: 0

Robert
Robert

Reputation: 25753

Try this way:

select distinct userid  
from yourtable
where userid not in (select userid 
                     from yourtable 
                     where status = 'True')

Upvotes: 2

Related Questions