I got 3 tables (from sakila sample db) : film, film_actor and actor.
Film: (1000 rows) film_id | title | .... ______________________ 1 | a | .... 2 | b | .... 3 | c | .... 4 | d | .... 5 | e | .... ......................
Film_actor:(5000+ rows) actor_id | film_id | .... ______________________ 1 | 12 | .... 1 | 33 | .... 2 | 210 | .... 3 | 2 | .... 3 | 500 | .... ......................
Actor:(~200 rows) actor_id | first_name | last_name __________________________________ 1 | a | f 2 | b | g 3 | c | h 4 | d | i 5 | e | l ...............................
Is there a way to get a result that looks like this:
title(film) | actors(first_name, last_name) _________________________________________________________________________ title 1 | first_name1 last_name1, first_name2 last_name2, etc.. title 2 | first_name1 last_name1, first_name2 last_name2, etc.. title 3 | first_name1 last_name1, first_name2 last_name2, etc.. title 4 | first_name1 last_name1, first_name2 last_name2, etc.. title 5 | first_name1 last_name1, first_name2 last_name2, etc.. .........................................................................
i.e. getting the film's list ordered by ID with all the actors playing in the movie into the 2nd column.
I've already checked but no answer on Stack Overflow seems to fit my question since the nested concatenation.
I need to get this result only via a MySQL query.
select `title`, group_concat(concat(first_name, ' ', last_name)) as actors
from film inner join film_actor on film.film_id = film_actor.`film_id`
inner join actor on film_actor.actor_id = actor.actor_id
group by `title`