Clever Geek Handbook

Selecting rows in SQL only where * all * values ​​do not meet 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 am having problems that I will only select users that do not have rows with Status

assigned True

.

The result will return user IDs 1 and 3.

I have a feeling it requires more than a direct selector WHERE

and experimented with GROUP BY and COUNT without success.

+3


source to share


4 answers


You can use GROUP BY

with a sentence HAVING

to get the result. As HAVING

you can use an expression CASE

with the unit for filtering all lines with 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

+7


source


Try this way:



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


source


This should work. 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)
0


source 


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'
0


source






More articles:


All Articles