Wesley Egbertsen Wesley Egbertsen - 1 year ago 60
SQL Question

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.



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


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

Answer Source

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