MySQL select all the ids with the same column value

Question

Here is my table:

movies_franchies:

movie_id franchise_id
1 10
2 11
3 10
4 15
1 11

What i want: get all movie_id with the same franchise_id as my target movie (movie_id - 1 for example) except target movie. As you may understand, this is some kind query to get similar movies based on franchise of selected movie.

I've tried:

SELECT movie_id 
FROM movies_franchises 
WHERE franchise_id = ( SELECT franchise_id 
 FROM movies_franchises 
 WHERE movie_id = @target_movie_id) 
AND movie_id != @target_movie_id;

But I don't think this is a good practice. Also, this query won't work if select subquery has more than one result (if movie has more than 1 franchise).

So in this case, if I want to get a similar movies for movie with movie_id = 1, it should be all movies from 10 AND 11 franchises.

Answer 1

If the subquery can return multiple results then you should use IN rather than =. It's good to get into the habit of fully qualifying which table a column is coming from:

select m.movie_id 
from movies_franchises m
where m.franchise_id in (select sq.franchise_id 
 from movies_franchises sq
 where sq.movie_id = @target_movie_id
 ) 
and m.movie_id != @target_movie_id

You could also just do a plain join.

select mf2.movie_id 
from movies_franchises mf1
join movies_franchises mf2
 on mf1.franchise_id = mf2.franchise_id
 and mf1.movie_id <> mf2.movie_id
where mf1.movie_id = @target_movie_id

This doesn't require anything further (like distinct) as `(franchise_id, movie_id) is already unique.

With regards to "But i don't think this is a good practice", this sort of query when you want a semijoin is fine - you only want columns from one of the row sources so you don't really need to do it as a join. In terms of performance, there's no rule as to which is better, and there's every chance that they both will use the same execution plan to carry out. That's not to say you won't find (very confident) advice saying you should only do it one particular way. The best query becomes the one that is easiest to read and maintain (which is whatever your boss decides ;) ).