Question: 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

```
If you want have `catagory_id` 1 and 2 use this:

```sql
select movie_id from your_table
group by movie_id
having string_agg(catagory_id::text, ',' order by catagory_id) ~ '(1,)+(2)+'

```

If you want have `catagory_id` 1 or 2 use this:

```sql
select distinct(movie_id) from your_table
where catagory_id in (1, 2)

```