I have a table with 2 columns:
| # | movie_id | episode |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 1 |
| 5 | 2 | 1 |
| 6 | 2 | 1 |
| 7 | 3 | 23 |
| 8 | 3 | 23 |
As you can see, the movie_id = 1 has 3 episodes and the episode numbers are correct, but the movie_id = 2 also has 3 episodes, but by mistake, episode numbers are duplicated.
Is there a way to get all the movie_ids that have duplicated episode numbers?
The output in this example should be:
2,3
CodePudding user response：
One simple approach uses aggregation:
SELECT movie_id
FROM yourTable
GROUP BY movie_id
HAVING COUNT(*) <> COUNT(DISTINCT episode);
The criterion in the HAVING clause ensures that every episode associated with a given movie_id is unique.