Using in paramenter in subquery?

Question

Is posible to using in paramenter in subquery?

I'm trying to join three table, and using paramenter in subquery, but I'm getting error:

drop procedure if exists displayFilmInfo;
delimiter //
create procedure displayFilmInfo(in in_category_id tinyint, in in_language_id tinyint)
begin

    if in_category_id != 0  and in_language_id != 0 then
        select film.title, after_category.name from film
            inner join film_category on film_category.film_id = film.film_id
            inner join (
                select category_id, name from category when category.category_id =  in_category_id
            ) after_category on after_category.category_id = film_category.category_id
            inner join (
                select language_id, name from language when in_language_id = language.language_id
            ) after_language on after_language.language_id = film.language_id
    end if;

end //
delimiter ;

call displayFilmInfo();

It show:

Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'when category.category_id = in_category_id ) after_category on afte' at line 8 ERROR 1305 (42000): PROCEDURE sakila.displayFilmInfo does not exist

Answer 1

Please update your select statement to -

select film.title,
       after_category.name
from film
inner join film_category on film_category.film_id = film.film_id
inner join (select category_id,
                   name
            from category
            where category.category_id =  in_category_id) after_category on after_category.category_id = film_category.category_id
inner join (select language_id,
                   name
            from language
            where in_language_id = language.language_id) after_language on after_language.language_id = film.language_id