MySQL LIMT is a subquery?

user_18094881 0
Viewed 27

I want to use a select statement to control the limit of another select query, I cant get it to work, what I have below. The Select in the braces returns an INT. Im newer to MySQL so im not sure what I should use instead to get this to work.

SELECT * 
FROM `tbl_prod` 
WHERE prod_id = 32 
ORDER BY prod_level ASC , prod_date 
LIMIT 
    (SELECT max_count 
    FROM Prod_subscription 
    WHERE prod_id = 32)
subquery limit select mysql
1 Answers

You can't write subquery in LIMIT, but you can use dynamic SQL to make your expected result.

SET @num = (
    SELECT max_count 
    FROM Prod_subscription 
    WHERE prod_id = 32);

PREPARE STMT FROM 'SELECT * 
FROM `tbl_prod` 
WHERE prod_id = 32 
ORDER BY prod_level ASC , prod_date 
LIMIT ?';
EXECUTE STMT USING @num;
user_51760710
Related