Get movies that have categories

I have associative table movies and their categories.

movie_id catagory_id
   1         1
   1         2
   1         3
   2         1
   2         2
   2         4
   3         1
   3         5
   3         6

How can i get movies that have category 1 and 2?

Result:

movie_id
   1
   2

Answer

Do GROUP BY. Use HAVING with COUNT DISTINCT to make sure both 1 and 2 are there.

select movie_id
from table
where category_id in (1, 2)
group by movie_id
having count(distinct category_id) = 2

Just for fun, you can also do a self join:

select m1.movie_id
from table m1
join table m2 on m1.movie_id = m2.movie_id
where m1.category_id = 1
  and m2.category_id = 2

Or, use INTERSECT:

select movie_id from table where category_id = 1
INTERSECT
select movie_id from table where category_id = 2