CODEWITHSUNDEEP
mysql
user892134
user892134

Reputation: 3224

MySQL GROUP BY if condition

I want to return movie_id ONLY if all rows with same movie_id has convert=2. If a row with same movie_id has convert=0 then don't return movie_id.

Example:

This should return nothing because all three rows have the same movie_id but convert=0 on the last row

+----+----------+------------+------------+--------------+---------+
| id | movie_id |    lang    | email_sent | user_request | convert |
+----+----------+------------+------------+--------------+---------+
| 1  |    1     |   german   |     0      |      1       |    2    |
| 2  |    1     |   french   |     0      |      1       |    2    |
| 3  |    1     |   english  |     0      |      1       |    0    |
+----+----------+------------+------------+--------------+---------+

This should return movie_id because all three rows have the same movie_id and convert=2 for all rows

+----+----------+------------+------------+--------------+---------+
| id | movie_id |    lang    | email_sent | user_request | convert |
+----+----------+------------+------------+--------------+---------+
| 1  |    1     |   german   |     0      |      1       |    2    |
| 2  |    1     |   french   |     0      |      1       |    2    |
| 3  |    1     |   english  |     0      |      1       |    2    |
+----+----------+------------+------------+--------------+---------+

Here is my query but isn't working when i try with second sample data, still returns movie_id

SELECT movie_id
from movie_subtitles
WHERE email_sent=0 AND user_request=1
GROUP BY CASE WHEN `convert`=2 THEN movie_id END
ORDER BY id ASC
LIMIT 1

How do i solve?

Upvotes: 1

Views: 47

Answers (1)

forpas
forpas

Reputation: 164064

You need a HAVING clause like this:

SELECT movie_id
FROM movie_subtitles
WHERE email_sent=0 AND user_request=1
GROUP BY movie_id
HAVING SUM(Convert <> 2) = 0

Upvotes: 2

Related Questions