Get movies that have categories [closed]-CodePudding

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

CodePudding user response：

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

CodePudding user response：

You should try this.

select movie_id 
from movies m 
join categories c on c.movie_id = m.movie_id and c.category_id in (1,2)

CodePudding user response：

If you want have catagory_id 1 and 2 use this:

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:

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

CodePudding user response：

Aggregate all the categories of each movie in an array and then filter those that contain all the target categories in having clause. The contains array operator is handy for the job.

 select movie_id from t
 group by movie_id
 having array_agg(catagory_id) @> array[1,2];