sql - Convert UNION selects to single select with loop in MySQL

Question

I have this ugly code here in MySQL 8.0.

(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Adventure'
        group by genre_name, actor_id 
        ORDER BY count(actor_id) DESC
        limit 3)        
UNION 
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Music'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Fantasy'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Romance'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Family'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Comedy'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Short'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Animation'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Action'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Horror'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Sci-Fi'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Thriller'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Crime'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Drama'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Documentary'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Musical'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Mystery'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='War'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Western'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Adult'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Film-Noir'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3);

and I want to convert it to be more concise. Basically, the only thing that changes is the genre_name condition. The 21 values for this condition can be found running: select genre_name from genre; How can I loop through the 21 values of genre_name and plug them into the select query, incrementally building up the UNION query? Thanks.

Answer 1

In MySQL (prior to 8.0), you can use variables for this:

select genre_name, actor_id, cnt
from (select genre_name, actor_id, cnt,
             (@rn := if(@g = genre_name, @rn + 1,
                        if(@g := genre_name, 1, 1)
                       )
             ) as rn
      from (select g.genre_name, r.actor_id, count(*) as cnt
            from movie m join
                 role r
                 on r.movie_id = m.movie_id join
                 movie_has_genre mhg
                 on mhg.movie_id = m.movie_id join
                 genre g
                 on g.genre_id = mhg.genre_id
            group by g.genre_name, r.actor_id
            order by g.genre_name, cnt desc
           ) ga cross join
           (select @g := '', @rn := 0) params
      ) ga
where rn <= 3;

In 8.0+, use row_number() the ANSI standard method for this functionality.

Notes:

• Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.
• Use table aliases that the abbreviations for the columns you are using.
• Qualify all column names in the queries that you are using, especially when the FROM clause references more than one table.