I have the following movie database relationship diagram and used the queries below to create the tables and its keys as shown below:
The following are the codes that I used to create these 4 tables and inserted the data:
CREATE TABLE my_movie
(movie_id int not null,
movie_name char(100) not null,
movie_released date not null,
primary key(movie_id))
CREATE TABLE person_name
(person_id int primary key,
first_name char(50)not null,
last_name char(50) not null,
birthdate date)
CREATE TABLE role
(role_id int primary key,
role_name char(50) not null)
CREATE TABLE movie_employee
(movie_id int not null,
person_id int not null,
role_id int not null,
primary key(movie_id, person_id, role_id),
FOREIGN KEY(movie_id) references my_movie(movie_id),
FOREIGN KEY(person_id) references person_name(person_id),
FOREIGN KEY(role_id) references role(role_id))
insert into role
VALUES(1, 'director');
insert into role
VALUES(2, 'actor');
insert into role
VALUES(3, 'actress');
insert into person_name
values(1, 'James', 'Cameron', '1954-8-16');
insert into person_name
values(2, 'Leonardo', 'DiCaprio');
insert into person_name
values(3, 'Kate', 'Winslet');
insert into person_name
values(4, 'Francis', 'Coppola');
insert into person_name
values(5, 'Marlon', 'Brando');
insert into person_name
values(6, 'Keanu', 'Reeves');
insert into person_name
values(7, 'Hugo', 'Weaving');
insert into my_movie
VALUES(1, 'Titanic', '1997-12-19');
insert into my_movie
VALUES(2, 'The Godfather', '1972-03-15');
insert into my_movie
VALUES(3, 'The Matrix', '1999-03-31');
insert into movie_employee,
VALUES(1, 1, 1);
insert into movie_employee
VALUES(1, 2, 2);
insert into movie_employee
VALUES(1, 3, 3);
insert into movie_employee
VALUES(2, 4, 1);
insert into movie_employee
VALUES(2, 5, 2);
insert into movie_employee
VALUES(3, 6, 2);
insert into movie_employee
VALUES(3, 7, 2);
Below are my 4 SELECT table illustration after inserting data:
TABLE my_movies:
TABLE movie_employee:
TABLE movie_name:
TABLE role:
The following join query gives me this out as shown below:
SELECT movie_name, movie_released, (role_name || ': ' || first_name || ' ' || last_name)as "Cast & Crew"
from my_movie
inner join movie_employee on my_movie.movie_id=movie_employee.movie_id
inner join role on movie_employee.role_id = role.role_id
inner join person_name on movie_employee.person_id = person_name.person_id
From this joined table, Titanic has been repeated 3 times, The Godfather repeated twice, listing the Cast&Crew members separately multiple times.
I want the Cast&Crew members (director, actor, actress) for the same movie to be concatenated into one row, like the highlighted joined table output below. How do I do this?
2
Answers
With the help of
grouping
,sub-query
andstring_agg()
function, the following could give you what you want. Note here, I couldn’t test the query onPostgreSQL
but it works onMicrosoft SQL Server
. So this is to give you an idea how to achieve your requirement.here’s my attempt with what seems to be the approriate results that you’re looking for :
using string_agg() to be able to group by movie_id.