I have a table user_test_access which stores test_id and user_id.

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.

Desired Result:

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?

CodePudding user response：

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

CodePudding user response：

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.