Bug #111095 Enhance film_list view by GROUP_CONCATing category.name column values
Description:
I hope some is involved with with Sakila sample database.
In the sakila-schema.sql, the `film_list` view defined like this.
```sql
CREATE VIEW film_list
AS
SELECT film.film_id AS FID,
film.title AS title,
film.description AS description,
category.name AS category,
film.rental_rate AS price,
film.length AS length,
film.rating AS rating,
GROUP_CONCAT(CONCAT(actor.first_name, _utf8mb4' ', actor.last_name) SEPARATOR ', ') AS actors
FROM film
LEFT JOIN film_category ON film_category.film_id = film.film_id
LEFT JOIN category ON category.category_id = film_category.category_id
LEFT JOIN film_actor ON film.film_id = film_actor.film_id
LEFT JOIN actor ON
film_actor.actor_id = actor.actor_id
GROUP BY film.film_id, category.name;
```
When a film is mapped to more than one category, that view contains multiple rows for each film.
Changing the DDL like this will be nicer.
```sql
CREATE VIEW film_list
AS
SELECT film.film_id AS FID,
film.title AS title,
film.description AS description,
GROUP_CONCAT(category.name SEPARATOR ', ') AS categories,
film.rental_rate AS price,
film.length AS length,
film.rating AS rating,
GROUP_CONCAT(CONCAT(actor.first_name, _utf8mb4' ', actor.last_name) SEPARATOR ', ') AS actors
FROM film
LEFT JOIN film_category ON film_category.film_id = film.film_id
LEFT JOIN category ON category.category_id = film_category.category_id
LEFT JOIN film_actor ON film.film_id = film_actor.film_id
LEFT JOIN actor ON film_actor.actor_id = actor.actor_id
GROUP BY film.film_id;
```
How to repeat:
Described in the Description section.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: I hope some is involved with with Sakila sample database. In the sakila-schema.sql, the `film_list` view defined like this. ```sql CREATE VIEW film_list AS SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price, film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(actor.first_name, _utf8mb4' ', actor.last_name) SEPARATOR ', ') AS actors FROM film LEFT JOIN film_category ON film_category.film_id = film.film_id LEFT JOIN category ON category.category_id = film_category.category_id LEFT JOIN film_actor ON film.film_id = film_actor.film_id LEFT JOIN actor ON film_actor.actor_id = actor.actor_id GROUP BY film.film_id, category.name; ``` When a film is mapped to more than one category, that view contains multiple rows for each film. Changing the DDL like this will be nicer. ```sql CREATE VIEW film_list AS SELECT film.film_id AS FID, film.title AS title, film.description AS description, GROUP_CONCAT(category.name SEPARATOR ', ') AS categories, film.rental_rate AS price, film.length AS length, film.rating AS rating, GROUP_CONCAT(CONCAT(actor.first_name, _utf8mb4' ', actor.last_name) SEPARATOR ', ') AS actors FROM film LEFT JOIN film_category ON film_category.film_id = film.film_id LEFT JOIN category ON category.category_id = film_category.category_id LEFT JOIN film_actor ON film.film_id = film_actor.film_id LEFT JOIN actor ON film_actor.actor_id = actor.actor_id GROUP BY film.film_id; ``` How to repeat: Described in the Description section.