CODEWITHSUNDEEP
mysqlsql
Gaurav Khe
Gaurav Khe

Reputation: 105

How to find rows where all values of a column are same?

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.

id test_creator test_id user_id
1 0 1 901
2 0 1 903
3 0 2 904
4 0 2 905
5 0 3 906
6 1 3 907
7 0 3 908

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?

Upvotes: 2

Views: 61

Answers (2)

ahmed
ahmed

Reputation: 9201

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.

Upvotes: 1

derpirscher
derpirscher

Reputation: 17416

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

Upvotes: 2

Related Questions