CODEWITHSUNDEEP
mysqlselectdistinct
nikodemus
nikodemus

Reputation: 81

Mysql select distinct if column equal to otherwise dont select

I have a table like this:

   id | id_user | approved 
   -----------------
   1  | 15      | 1
   2  | 11      | 1
   3  | 15      | 1
   4  | 11      | -1
   5  | 6       | 1

I want to select distict values of id_user only if every approved of each id_users is equal to 1. Output:

   id | id_user | approved 
   -----------------
   1  | 15      | 1
   2  | 6       | 1

As you can see, id_user 11 is not selected because one approved value is equal to -1. How can i do this in mysql ?

I've tried SELECT * FROM table WHERE approved != -1 GROUP BY id_user but obviously it does not work so i've decided to ask here this question.

Thank you very much for your time.

//Later edit

I have one more question. I have to modify the query. I have one more table validation

id | id_user | valid
--------------------
1  | 15      | 1

which i have to join with the other table, but with a condition. For example, let's say that your query outputs(correctly):

id | id_user | approved 
-----------------
1  | 15      | 1
2  | 6       | 1

What i want is to join those 2 tables:

LEFT JOIN (SELECT va.id_user,va.valid FROM validation va WHERE va.valid != 1) t2 
ON t2.id_user = t1.id_user

but select only those rows for which valid is NOT equal to 1. It's very possible that id_user from the first table might not exist in the validation table. In this case, the query should return exactly what Alex's query outputs.

Let me give you an example. First table is the same:

 id | id_user | approved 
 -----------------
 1  | 15      | 1
 2  | 11      | 1
 3  | 15      | 1
 4  | 11      | -1
 5  | 6       | 1
 6  | 6       | 1

validation table:

 id | id_user | valid
 --------------------
 1  | 15      | 1

The query should output:

 id | id_user 
 ------------
 1  | 6

id_user = 15 is in second table with valid=1, so it won't be selected

id_user = 11 has a -1 value in approved field, so it won't be selected

id_user = 6 has 1 in both approved fields, but is not present in second table, i want it to be selected.

Upvotes: 0

Views: 500

Answers (1)

Alex
Alex

Reputation: 17289

Here is my approach: http://sqlfiddle.com/#!9/607e16/2

SELECT *
FROM t1
GROUP BY id_user
HAVING COUNT(*)=SUM(approved)

it doesn't return exact resultset as you described. But in fact you need just 

SELECT id_user
FROM t1
GROUP BY id_user
HAVING COUNT(*)=SUM(approved)

because htereis no rule and it doesn't matter which id or approved is returned.

Keep in mind this solution works only if approved can be just 1, 0, -1 if value could be 2,3,4,... etc this solution will not work.

UPDATE According to your 2nd question you can join validation table this way:

http://sqlfiddle.com/#!9/96b4b/1

SELECT t1.*
FROM t1
LEFT JOIN validation v
ON v.id_user = t1.id_user
GROUP BY t1.id_user
HAVING COUNT(*)=SUM(approved)
  AND SUM(v.valid) IS NULL

Upvotes: 2

Related Questions