How do I select distinct rows where a column may have a number of the same values but all their 2nd columns have the same value?

Question

I have a table in the form:

test_name | test_result
-----------------------
test1     | pass 
test2     | fail 
test1     | pass   
test1     | pass   
test2     | pass   
test1     | pass   
test3     | pass   
test3     | fail   
test3     | pass 

As you can see all test1's pass while test2's and test3's have both passes and fails.

Is there a SQL statement that I can use to return the distinct names of the tests that only pass? E.g. test1

Answer 1

This might not be the simplest way (I tend to abuse subqueries), but you can do:

SELECT test_name FROM tests AS a WHERE NOT EXISTS (SELECT test_name FROM tests AS b WHERE a.test_name = b.test_name AND test_result != 'pass') GROUP BY test_name;

Answer 2

You can group by the test_name, count the number of passes, and check if it is the same as the number of rows in the group.

SELECT test_name
FROM table1
GROUP BY test_name
HAVING SUM(test_result = 'pass') = COUNT(*)

Alternatively, count the number of fails and check that it is zero:

SELECT test_name
FROM table1
GROUP BY test_name
HAVING SUM(test_result = 'fail') = 0

Result:

test1

Test data:

CREATE TABLE table1 (test_name NVARCHAR(100) NOT NULL, test_result NVARCHAR(100) NOT NULL);
INSERT INTO table1 (test_name, test_result) VALUES
('test1', 'pass'),
('test2', 'fail'),
('test1', 'pass'),
('test1', 'pass'),
('test2', 'pass'),
('test1', 'pass'),
('test3', 'pass'),
('test3', 'fail'),
('test3', 'pass');