MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table movie (movie_id int,title varchar(50));
create table movie_cast(movie_id int,person_id int);
create table movie_crew(movie_id int,person_id int);
create table person(person_id int,person_name varchar(50));
insert into movie values
 (1,'Movie 1')
,(2,'Movie 2')
;
insert into person values
 (10,'Carlson')
,(11,'Peterson')
,(12,'Henderson')
,(13,'Magnuson')
;
insert into movie_cast values
 (1,10)
,(1,11)
,(2,11)
,(2,12)
;
insert into movie_crew values
 (1,10)
,(1,12)
,(2,11)
,(2,13)
;
select * from movie;
select * from person;
select * from movie_cast;
select * from movie_crew;

Records: 2  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0




movie_id
title


1
Movie 1

2
Movie 2




person_id
person_name


10
Carlson

11
Peterson

12
Henderson

13
Magnuson




movie_id
person_id


1
10

1
11

2
11

2
12




movie_id
person_id


1
10

1
12

2
11

2
13


select m.movie_id,title
  ,m1.person_name cast_member
  ,m2.person_name crew_member
from movie m
left join 
  (
select movie_id
  ,min(case when pt='cast' then person_id end) cast_id
  ,min(case when pt='crew' then person_id end) crew_id
from(
select movie_id,person_id, 'cast' as pt from movie_cast
union all
select movie_id,person_id, 'crew' as pt from movie_crew
)cast_and_crew
group by movie_id,person_id
)pcc on pcc.movie_id=m.movie_id
left join person m1 on m1.person_id=pcc.cast_id
left join person m2 on m2.person_id=pcc.crew_id
order by m.movie_id,coalesce(m1.person_id,m2.person_id)


movie_id
title
cast_member
crew_member


1
Movie 1
Carlson
Carlson

1
Movie 1
Peterson
null

1
Movie 1
null
Henderson

2
Movie 2
Peterson
Peterson

2
Movie 2
Henderson
null

2
Movie 2
null
Magnuson


SELECT 
    m.title,
    pc.person_name AS cast_member,
    pr.person_name AS crew_member
FROM movie m
left JOIN movie_cast mc ON m.movie_id = mc.movie_id
left JOIN movie_crew mcc ON m.movie_id = mcc.movie_id and mcc.person_id=mc.person_id
left JOIN person pc ON mc.person_id = pc.person_id
left JOIN person pr ON mcc.person_id = pr.person_id 
order by m.movie_id,mc.person_id,mcc.person_id;


title
cast_member
crew_member


Movie 1
Carlson
Carlson

Movie 1
Peterson
null

Movie 2
Peterson
Peterson

Movie 2
Henderson
null


SELECT 
    m.title,
    pc.person_name AS cast_member,
    pr.person_name AS crew_member
FROM movie m
JOIN movie_cast mc ON m.movie_id = mc.movie_id
JOIN person pc ON mc.person_id = pc.person_id
JOIN movie_crew mcc ON m.movie_id = mcc.movie_id
JOIN person pr ON mcc.person_id = pr.person_id
order by m.movie_id,mc.person_id,mcc.person_id;


title
cast_member
crew_member


Movie 1
Carlson
Carlson

Movie 1
Carlson
Henderson

Movie 1
Peterson
Carlson

Movie 1
Peterson
Henderson

Movie 2
Peterson
Peterson

Movie 2
Peterson
Magnuson

Movie 2
Henderson
Peterson

Movie 2
Henderson
Magnuson
movie_id	title	cast_member	crew_member
1	Movie 1	Carlson	Carlson
1	Movie 1	Peterson	null
1	Movie 1	null	Henderson
2	Movie 2	Peterson	Peterson
2	Movie 2	Henderson	null
2	Movie 2	null	Magnuson