Xtykutl

I have a scenario where the result must be limited depends on counting ids in another table.

Suppose i have these two tables counter and dispanser,

i want to select the last records in the table counter and limit the selection by counting the number of records in dispanser table.

something like this

select * from counter limit (select count(dispID) from dispanser) 

You can't do this without using prepared statements or a stored procedure. From the manual:

LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants

In a stored procedure you could do something like this. COUNT(dispID) is stored into cnt and that variable is then used as the parameter to LIMIT. This is the exception to the above-mentioned rule.

DELIMITER //

CREATE PROCEDURE select_counter()

BEGIN

  DECLARE cnt INT;

  SELECT COUNT(dispID) INTO cnt FROM dispanser;

  SELECT * FROM counter LIMIT cnt;

END //

DELIMITER ;

DBFiddle

Based on conversations in the comments, it sounds like what you're trying to do is get the count from counter for each record in dispanser - if this is wrong, please comment, and I can adjust my response. The best way to accomplish what you're looking for is through joining a subquery with the GROUP BY syntax. Something like this might could work, depending on your schema:

SELECT

   d.*,

   c.total

FROM

   dispanser as d

INNER JOIN (

   SELECT

      COUNT(*) as 'total',

      dispID

   FROM

      counter

   GROUP BY

      dispID

   ) as c

   ON c.dispID = d.id

You can try to use dynamic SQL.

Set a variable @num to get an amount from dispanser table.

prepared your SQL statement CONCAT('select * from counter limit ', @num ).

Final use EXECUTE to execute SQL dynamically.

SET @sql = CONCAT('select * from counter order by counterID desc limit ', (SELECT count(dispID) from dispanser));



PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

sqlfiddle