Skip to content
Advertisement

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:

JavaScript

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?

Advertisement

Answer

Here is one solution that uses aggregation:

JavaScript

This demo on DB Fiddle with your sample data returns:

JavaScript
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement