How can i write a query that retrieves the series of a specific movie

Question

I'm using the IMDB database and i'm stuck with the following problem:

Get all the series of a specific movie (with it's previous part):

If you take a look at the movies table below you can see that some records have a previous_part. It's referencing to the movieId of the same table.

For example: how can i fetch the lord of the rings movies which has a previous part and also fetch that previous_part etc..

What i have so far is this:

;with SequenceChilds AS 
(
  SELECT movie_id, previous_part, title
  FROM Movie
  WHERE movie_id = 194502

  UNION ALL

  SELECT m.movie_id, m.previous_part, m.title
  FROM Movie m
  inner JOIN SequenceChilds p ON p.previous_part = m.movie_id
) 
select * from SequenceChilds

This is giving me the following result:

It seems like it isn't iterating deeply enough, because what i expect from the result is to give back the following:

Much appreciated if someone can point me to the right direction. Thanks in advance!

Please give some feedback before downvoting my post. I can always update my question. — O.S.Kaya, Jun 29 '19 at 09:52
Update the question with your expected output and also post the query you are trying with. — mkRabbani, Jun 29 '19 at 10:38
It would be better to define a Series and then place all the parts of the movie into a series. And then you can set a position field in each movie to define its place in the series. That would be trivial to query compared to this linking structure — ADyson, Jun 29 '19 at 10:47
@mkRabbani I just updated the post with what i have so far and the expected result — O.S.Kaya, Jun 29 '19 at 10:56
I agree, a "series" should be defined in as a data item in the data, it shouldn't be inferred by the film being a sequel to a sequel. Say you have a series with 9 films an you have the 4th film, you'd have to recurse both up and down the "hierarchy" rather than just getting details of films in the same series by some kind of ID or name. — Thom A, Jun 29 '19 at 11:12
You need a recursive loop/join. This is typically done in the form of a cte. See here for a comparable issue: https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example — Simon Wray, Jun 29 '19 at 11:54
@O.S.Kaya In the logic you applied, the recursive query only returns the movie series down the movie ID you passed because it transverse down the hierarchy, please check the answer I provided which uses the parent movie id to get the complete series — MJoy, Jul 01 '19 at 05:10

score 2 · Accepted Answer · answered Jul 01 '19 at 05:05

The logic here is to first find the Parent Movie ID for the any Movie ID passed as input and then using a recursive query to roll down from parent level to find all child levels, The following query should do what you want:

CREATE TABLE #movie ([Movie_ID] INT, [Previous_Part] INT, [Title] VARCHAR(25))

INSERT INTO #movie ([Movie_ID], [Previous_Part], [Title])
VALUES
    (194500, NULL, 'Part 1'),
    (194501, 194500, 'Part 2'),
    (194502, 194501, 'Part 3'),
    (194503, 194502, 'Part 4'),
    (194504, 194503, 'Part 5')

/*** QUERY Starts Here ***/

DECLARE @SerchID INT 
SET @SerchID  = 194503 -- Movie to search

;WITH Parent AS
(
    SELECT *, 1 AS Lvl FROM #movie 
    WHERE [Movie_ID] = @SerchID

    UNION ALL

    SELECT mv.*, Lvl+1 AS Lvl FROM #movie mv
    INNER JOIN Parent p ON mv.[Movie_ID] = p.[Previous_Part] 
)
,RCTE AS 
(
    SELECT mv.* FROM #movie mv
    WHERE [Movie_ID] = (SELECT TOP 1 [Movie_ID] FROM Parent ORDER BY Lvl DESC)

    UNION ALL

    SELECT mv.* FROM #movie mv
    INNER JOIN RCTE rc ON rc.[Movie_ID] = mv.[Previous_Part] 
)

SELECT * FROM RCTE r

The result is as below,

Movie_ID    Previous_Part   Title
194500      NULL            Part 1
194501      194500          Part 2
194502      194501          Part 3
194503      194502          Part 4
194504      194503          Part 5

score 0 · Answer 2 · answered Jun 29 '19 at 11:53

It is not returning you all records as you are filtering the movies in first section of CTE. This query can provide you with all movies along with their sequels,

;With MoviesWithSequels AS
(
SELECT m.movie_id, m.previous_part, m.title
FROM Movie m WHERE m.previous_part is not null
)
SELECT * FROM MoviesWithSequels

UNION

SELECT p.movie_id, p.previous_part, p.title FROM Movie p 
INNER JOIN MoviesWithSequels MS ON p.movie_id = MS.previous_part;

And then you can filter it based on Titles in CTE as follows,

;With MoviesWithSequels AS
(
SELECT m.movie_id, m.previous_part, m.title
FROM Movie m WHERE m.previous_part is not null AND m.Title Like 'Lord of the Rings%'
)
SELECT * FROM MoviesWithSequels

UNION

SELECT p.movie_id, p.previous_part, p.title FROM Movie p 
INNER JOIN MoviesWithSequels MS ON p.movie_id = MS.previous_part;

Hope this helps.

How can i write a query that retrieves the series of a specific movie

2 Answers2