MySQL – Using Alias of Derived Table for Comparison Subquery

MySQLmysql-5.7

I have been working with MySQL for some time, however still learning best ways to use it.

For practice, I wrote a query to get the most featured film category for every actor in the sakila example database we can get with the workbench for MySQL. The result looks like this:

example result

I get the result with the following query. However, I had to repeat the same query twice, once in the from part of the outer query, and second time in the where part of a subquery.

select  `MCPA`.`first_name` ,`MCPA`.`last_name`,
        group_concat(`MCPA`.`category`) as `most_featured_categories`
    from  
    (
        SELECT  AC.first_name as `first_name` ,AC.last_name as `last_name` ,
                CA.`name` as `category` ,
                count( distinct FI.film_id ) as `number_of_movies` ,
                count( distinct FI.film_id ) /
                     fNumber_Of_Films_Per_Actor (AC.first_name, AC.last_name) * 100
                    as `percentage_of_movies_done`
            from  actor as AC
            inner join  film_actor as FIAC  ON AC.actor_id = FIAC.actor_id
            inner join  film as FI  ON FIAC.film_id = FI.film_id
            inner join  film_category as FICA  ON FI.film_id = FICA.film_id
            inner join  category as CA  ON FICA.category_id = CA.category_id
            group by  AC.first_name, AC.last_name, CA.`name` 
    ) as `MCPA`
    where  `MCPA`.`number_of_movies` = 
    (
        SELECT  max(`SubMCPA`.`number_of_movies`)
            from  
            (
                SELECT  AC.first_name as `first_name`, AC.last_name as `last_name`,
                        CA.`name` as `category` ,
                        count( distinct FI.film_id ) as `number_of_movies` ,
                        count( distinct FI.film_id ) /
                             fNumber_Of_Films_Per_Actor (AC.first_name,
                                AC.last_name
                             ) * 100
                           as `percentage_of_movies_done`
                    from  actor as AC
                    inner join  film_actor as FIAC  ON AC.actor_id = FIAC.actor_id
                    inner join  film as FI  ON FIAC.film_id = FI.film_id
                    inner join  film_category as FICA  ON FI.film_id = FICA.film_id
                    inner join  category as CA
                          ON FICA.category_id = CA.category_id
                    group by  AC.first_name, AC.last_name, CA.`name` 
            ) as `SubMCPA`
            where  1=1
              and  `MCPA`.`first_name` = `SubMCPA`.`first_name`
              and  `MCPA`.`last_name` = `SubMCPA`.`last_name` 
    )
    group by  `MCPA`.`first_name`, `MCPA`.`last_name` ;

I had tried to use 

    select max(`SubMCPA`.`number_of_movies`)
    from `MCPA` as `SubMCPA`
    where 1=1
        and `MCPA`.`first_name` = `SubMCPA`.`first_name`
        and `MCPA`.`last_name` = `SubMCPA`.`last_name`

But it didn't work; seems that workbench does not recognize the derived table, and requires me to redefine it.

Do I have some other way to avoid typing the code

    (
        select AC.first_name as `first_name`
            ,AC.last_name    as `last_name`
            ,CA.`name` as `category`
            ,count( distinct FI.film_id ) as `number_of_movies`
            ,count( distinct FI.film_id ) /
                    fNumber_Of_Films_Per_Actor (AC.first_name, AC.last_name) * 100
                  as `percentage_of_movies_done`
        from actor as AC
        inner join film_actor as FIAC
        on AC.actor_id = FIAC.actor_id
        inner join film as FI
        on FIAC.film_id = FI.film_id
        inner join film_category as FICA
        on FI.film_id = FICA.film_id
        inner join category as CA
        on FICA.category_id = CA.category_id
        group by AC.first_name, AC.last_name, CA.`name`
     ) as

twice?

** I have tried to avoid it by first creating a table and then referencing it, however maybe there is some other way?

Moreover seems having all done in one query increases performance.

Any advice would be appreciated, hope my question was clear, I refrained describing all of the database structure to avoid a too tedious question.

Best Answer

Does something like this give you the answer?:

select  `MCPA`.`first_name` ,`MCPA`.`last_name`,
        group_concat(`MCPA`.`category`) as `most_featured_categories`
    from  
        (  SELECT
               ...
        ) AS MCPA
    ORDER BY MCPA.`number_of_movies` DESC
    LIMIT 1;

For possible added performance, see my tips on many:many tables .

Related Question