Here's an example of the set up I'm using:
TITLES
id
name
DIRECTORS
id
name
ACTORS
id
name
MOVIES
id
movie_id
director_id
There can be several actors to a movie, so I use another join table:
MOVIES_ACTORS
id
movie_id
actor_id
This all works fine, except for one problem... i get several rows for one movie. So it comes out like this:
Star Wars - George Lucas - Harrison Ford
Star Wars - George Lucas - James Earl Jones
Star Wars - George Lucas - Yoda
King Kong - Peter Jackson - Jack Black
School of Rock - NULL - Jack Black
This is a problem because I don't want 3 results for Star Wars, I want one result row with 3 columns for the actors. Have three separate rows causes problems because:
1. If I were to limit the results to say, 3 I would only get Star Wars as a result.
2. When I display the results with PHP I have to build an elaborate array and loop through the result set 3 times just to avoid showing more than one result for the same movie in the list.
Here's my query:
SELECT
m.id, t.name, dir.name, act.name
FROM movies m
JOIN titles t ON t.id = m.title_id
LEFT OUTER JOIN directors dir ON dir.id = m.director_id
LEFT OUTER JOIN movies_actors ma ON ma.movie_id = m.id
LEFT OUTER JOIN actors a ON ma.actor_id = a.id
---------------------------------------------------------------
The solution is a GROUP_CONCAT on the terms you want to combine
SELECT m.id, t.name,
group_concat(dir.name) AS directors, group_concat(act.name) AS actors
FROM movies AS m
JOIN titles AS t ON t.id = m.title_id
LEFT JOIN directors AS dir ON dir.id = m.director_id
LEFT JOIN movies_actors AS ma ON ma.movie_id = m.id
LEFT JOIN actors AS a ON ma.actor_id = a.id
GROUP BY t.name
It combines those columns as a comma-separated list. You can also define a
separator...
GROUP_CONCAT(dir.name SEPARATOR '; ')
