SELECT with INNER JOIN, GROUP BY and LIMIT

Question

I have a three tables about movies. First one is movie, second one is actor and last one is movie_actor_mapping. I wrote an query that select data from table movie and group actors for each movie. And my query select all actors from actor table but I have to select only first 4 actors. How can I modify my select to do it?

SELECT title, year, poster, rating, group_concat(a.name separator ', ') as actors
FROM movie m
INNER JOIN movie_actor_mapping ma ON m.movie_id = ma.movie_id
INNER JOIN actor a ON a.actor_id = ma.actor_id
WHERE m.rating IS NOT NULL
GROUP BY m.movie_id
ORDER BY m.rating DESC;

Tables don't have any ordering, you have to use `ORDER BY` to specify the order in a query. — Barmar, Dec 08 '16 at 20:12
Anyway, see See http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group for how to for how to get 4 actors per movie. Join that with the movie table. — Barmar, Dec 08 '16 at 20:13

score 1 · Accepted Answer · answered Dec 08 '16 at 20:53

1

You can hack it around by substringing group_concat output to first 3 results, e.g.:

select m.name as movie, substring_index(group_concat(a.name SEPARATOR ','), ',', 3) as actors
from movie m
join movie_actor ma on m.id = ma.movie_id
join actor a on ma.actor_id = a.id
group by m.id
order by m.id, a.id;

This would give you 3 actors per movie.

Here is the SQL Fiddle.

answered Dec 08 '16 at 20:53

Darshan Mehta

30,102
11
68
102

Thank you so much! – rel1x Dec 08 '16 at 21:51

SELECT with INNER JOIN, GROUP BY and LIMIT

1 Answers1