SQL Select Statement on 1:M Recursive Relationship

user_17527939 0
Viewed 123

I have the following table:

MovieID movieName parentMovieID
1 Action NULL
2 Romace NULL
3 Comedy NULL
4 Movies A 1
5 Movie B 1
6 Movie C 2
7 Movie D 2
8 Movie E 2
9 Movie F 3
10 Movie G 3
11 Movie H 3

I've been trying to output the movie name based on the ID to match the table below but I'm having trouble with the select statement.

MovieID movieName parentMovieID
1 Action NULL
2 Romace NULL
3 Comedy NULL
4 Movies A Action
5 Movie B Action
6 Movie C Romace
7 Movie D Romace
8 Movie E Romace
9 Movie F Comedy
10 Movie G Comedy
11 Movie H Comedy
mysql sql
2 Answers

You can use a left join:

select m.*, m1.moviename genre from movies m 
left join (select m2.* from movies m2 where m2.parentmovieid is null) m1 
on m.parentmovieid = m1.movieid
user_73267380

A typical LEFT JOIN will provide the info you want:

select a.movieID, a.movieName, b.movieName
from movie a
left join movie b on b.MovieID = a.parentMovieID
user_64361910
Related