Блог

У меня есть база данных со следующими таблицами:

• фильмы: (идентификатор, название)
• субъект: (идентификатор, имя)
• директор: (удостоверение личности, имя)
• movies_actors: (movie_id, actor_id)
• movies_directors: (movie_id, director_id)

Последние две таблицы представляют отношения между фильмами и актерами и режиссерами. Я хочу получить список фильмов, включая их актеров и режиссеров, разделенных запятыми.

Я попробовал следующее, чтобы получить эту информацию, например:

 SELECT movie.title,
GROUP_CONCAT((SELECT name FROM actors WHERE id=movies_actors.actor) SEPARATOR ', ') AS 'actors',
GROUP_CONCAT((SELECT name FROM directors WHERE id=movies_directors.director) SEPARATOR ', ') AS 'directors'
FROM movies
LEFT JOIN movies_actors ON movies_actors.movie = movies.id
LEFT JOIN movies_directors ON movies_directors.movie = movies.id
 

Однако это возвращает результат следующим образом:

Несмотря на то, что в фильме только один режиссер, он отображается 3 раза. Я хотел бы получить следующий результат:

Как мне это сделать?

Лучшее решение — это подзапросы. Для этой цели вы можете использовать коррелированные подзапросы:

 SELECT m.title,
       (SELECT GROUP_CONCAT(a.name, SEPARATOR ', ')
        FROM movies_actors ma JOIN
             actors a
             ON a.id = ma.actor
        WHERE ma.movie = m.id
       ) AS actors,
       (SELECT GROUP_CONCAT(d.name, SEPARATOR ', ')
        FROM movies_directors md JOIN
             directors d
             ON d.id = md.director
        WHERE md.movie = m.id
       ) AS directors
FROM movies m;
 

Обратите внимание, что здесь происходит. Для каждого фильма каждый подзапрос собирает все интересующие строки и объединяет имена в один столбец. Во внешнем запросе агрегирование не требуется, поскольку каждый подзапрос возвращает ровно одну строку.

Когда вы объединяетесь в таблицах соединений во внешнем запросе, вы получаете декартово произведение актеров и режиссеров для каждого фильма — и это отбрасывает результаты.