SQL Select Statement on 1:M Recursive Relationship-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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