MySQL search with selected options only

Question

I have three tables movies, categories & movie_categories.

Table: Movies

#	title
1	movie - 1
2	movie - 2
3	tv - 1

Table: Categories

#	title
1	Horror
2	Comedy
3	(TV)
4	Sci-fi

Table: Movie_Categories

#	movie_id	category_id
1	1	1
2	2	4
3	3	3
4	3	4

I am trying to fetch the records which only has movie_categories.category_id: 3 & 4

SELECT `movies`.`title` FROM `movie_categories`
INNER JOIN `movies` ON `movies`.`id` = `movie_categories`.`movie_id`
WHERE `category_id` = 3
AND `category_id` = 4

If I use OR instead of AND then it shows all the records which has either of the selected categories.

What am I doing wrong..?

Answer 1

3 and 4 cannot be valid in a single row. So, your where clause filters out all rows.

You want values in different rows, so thing aggregation -- and then filter after aggregation:

SELECT mc.movid_id
FROM movie_categories mc
WHERE mc.category_id IN (3, 4)
GROUP BY mc.movie_id
HAVING COUNT(*) = 2;   -- both rows are present

Note that your query is also referring to title, but that is not in the movie_categories database. You need a JOIN (well, there are other ways but that is the most common) to get the title from movie_id. I'll let you work on that.