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

>Solution :

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;