How to find rows where all values of a column are same?
I have a table
user_test_access which stores
user_test_access table stores all the uses who have access to the test as well as which user created the test.
I want a query to return all the
test_id where there is no creator. i.e test_creator = 0.
For the particular data set the answer would be
test_id 1 and 2.
The reason test_id 3 is not included is because
user_id 907 is the
test_creator for it.
What I’ve tried:
SELECT test_id from user_test_access WHERE id = ALL(SELECT id from user_test_access WHERE test_creator=0)
Can you please help me figure out what I’m doing wrong?
Solution – 1
If a missing testcreator is encoded by the value
0, you can just group by the
test_id and select only ids where the sum is zero
select test_id from user_test_table group by test_id having sum(test_creator) = 0
Solution – 2
You can use not exists operator as the following:
SELECT DISTINCT test_id FROM user_test_access T WHERE NOT EXISTS (SELECT 1 FROM user_test_access D WHERE D.test_id=T.test_id AND D.test_creator=1)
See a demo.