Question 21910 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search