Q:

How to get all entities from a foreign key that points to an entity in the same table, reiterative?

I have a Movie table and a movie can have a previous_part. Now I would like to, for example, based on movie_id '412331' get all the movies as shown in the image with the Star Wars movies. Is this possible with SQL(MsSQL/AzureSql)?
I am sorry for the lack of information, but I have honestly no idea on how to even start making a SQL query with this problem.

Update:
Created a Recursive Query, but it only works if I give the movie that starts it all (see WHERE Prev.previous_part = 412332 in the code block). So in this example Episode V would return the other two movies and Episode VI would only return Episode VII.
With MovieList AS
    (SELECT Prev.movie_id, Prev.title, Prev.description, Prev.previous_part, 1 as PrevLevel
    FROM Movie as Prev
    WHERE Prev.previous_part = 412332

    UNION ALL

    SELECT Mov.movie_id, Mov.title, Mov.description, Mov.previous_part, ML.PrevLevel + 1
    FROM Movie as Mov
    INNER JOIN MovieList AS ML
    ON Mov.previous_part = ML.movie_id
    WHERE Mov.previous_part IS NOT NULL)
SELECT * FROM MovieList

A:

This is Recursive CTE you need:
;with ml as (
--this is Anckor Query
select movie_id, title, previous_part
from movie where movie_id = 412325
union all
--this is Recursive Query
select m.movie_id, m.title, m.previous_part
from movie m
inner join ml on ml.previous_part = m.movie_id --link current prev to parent id
--if you want sequels instead of prev's change to m.previous_part = ml.movie_id
)
select * from ml

