Let’s say i have this table
One movie can have multiple franchises, so i need to get all movies with the same franchises. Like if i want to get all similar movies for the movie with id 23, i should get 24 25 26.
This query works, but i’m just wondering if this is the right way of doing it
SELECT DISTINCT movie_id FROM movies_franchises WHERE franchise_id IN (SELECT franchise_id FROM movies_franchises WHERE movie_id = 23) AND movie_id != 23
Your current query is fine, but you could also express this using a join:
SELECT m2.movie_id FROM movies_franchises m1 INNER JOIN movies_franchises m2 ON m2.franchise_id = m1.franchise_id WHERE m1.movie_id = 23 AND m2.movie_id <> 23;