skip to Main Content

I have the following movie database relationship diagram and used the queries below to create the tables and its keys as shown below:

Image of Database

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:

enter image description here

TABLE movie_employee:

enter image description here

TABLE movie_name:

enter image description here

TABLE role:

enter image description here

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

enter image description here

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?

enter image description here

2

Answers


  1. With the help of grouping, sub-query and string_agg() function, the following could give you what you want. Note here, I couldn’t test the query on PostgreSQL but it works on Microsoft SQL Server. So this is to give you an idea how to achieve your requirement.

    SELECT movie_name, movie_released, (
        SELECT 
            string_agg(TRIM(role_name)+': '+TRIM(first_name) +' '+ TRIM(last_name),', ') 
        FROM movie_employee 
            inner join role on movie_employee.role_id = role.role_id
            inner join person_name on movie_employee.person_id = person_name.person_id
        where movie_employee.movie_id = my_movie.movie_id
    
        group by movie_employee.movie_id
    ) [Cast & Crew]
    from my_movie
    

    enter image description here

    Login or Signup to reply.
  2. here’s my attempt with what seems to be the approriate results that you’re looking for :

    SELECT movie_name, movie_released,(
    select string_agg(role_name || ': ' || first_name || ' ' || last_name,',')
    from movie_employee me
    inner join "role" on me.role_id = "role".role_id
    inner join person_name on me.person_id = person_name.person_id
    where me.movie_id = my_movie.movie_id
    group by me.movie_id)as "Cast & Crew"
    from my_movie
    

    using string_agg() to be able to group by movie_id.

    Result of Query pasted above

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search