How to get list of movies based on genre and language

I have stuck to get list of movies based on genre and language.

Here’s my database structure:

    movies table
-----------------------
id | title|
----------------------
1  | ABC
2  | PQR
3  | MNC

category table
-----------------------
id | catgory  | value
----------------------
1  | language |Hindi
2  | language |Endilish
3  | genre    |Drama
4  | genre    |Action
5  | genre    |Thriller

manage table
-----------------------
id|catgory_id|movie_id
----------------------
1  |     1   |  1
2  |     3   |  1
3  |     4   |  1
4  |     2   |  2
5  |     4   |  2

Here’s what i trying to do I want movies with Hindi language with genre of Action.

Below is my query:

SELECT * 
FROM manage 
WHERE category_id='1' AND category_id='4' 
GROUP BY movie_id

I was expecting result of Hindi Movies with genre of Action but it returns null result.

But when I use OR operator in query it returns result but with movies which has language English also but expected is language Hindi and Genre Action.

Where am I going wrong?

Answer

Here is one solution that uses aggregation:

SELECT m.id, m.title
FROM movie m
INNER JOIN manage a   ON a.movie_id = m.id
INNER JOIN category c ON a.category_id = c.id
GROUP BY m.id, m.title
HAVING 
    MAX(c.category = 'language' AND c.value = 'Hindi') = 1
    AND MAX(c.category = 'genre' AND c.value = 'Action') = 1

This demo on DB Fiddle with your sample data returns:

| id  | title |
| --- | ----- |
| 1   | ABC   |

Advertisement

Answer