Mysql limit results based on value from column

Question

I'm trying to limit the number of results of a query based on another table column. For example, I have a table for products and a config table, like this:

tb_product
id | active | name | value | ...

tb_config
max_product | ...

What I'd like to do is something like this

SELECT 
    a.name, a.value 

FROM 
    tb_product a,
    tb_config b 

WHERE a.active = 1
LIMIT b.max_product

But I'm getting errors like #1327 - Undeclared variable: b. Is there a way to achieve this result?

Because currently what I'm doing is doing another query to get just the max_product value and then use it as php variable to limit the results, like this:

$limit = "SELECT max_product FROM tb_config";
SELECT name, value FROM tb_product WHERE ativo = 1 LIMIT $limit

@LelioFaieta what do you mean? The table `tb_config` is where I set some default configurations to control how many items I want to show on the page (in this example). — celsomtrindade, Apr 12 '18 at 13:34
To do so, you need to `JOIN` the table. See [this article](https://www.w3schools.com/sql/sql_join.asp) or [What is the difference between “INNER JOIN” and “OUTER JOIN”?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join/38578#38578) for more information — fictimaph, Apr 12 '18 at 13:34
does tb_config only always have 1 record? As it stands you're cross joining tb_product and tb_config so if product has 10 records and config 2 your results will have 20; when you likely only want 10. Additionally what datatype is max_product? — xQbert, Apr 12 '18 at 13:45
This previous question appears relevant:https://stackoverflow.com/questions/245180/variable-limit-clause-in-mysql — xQbert, Apr 12 '18 at 13:49

score 0 · Answer 1 · answered Apr 12 '18 at 13:52

0

Maybe....

SELECT a.name
     , a.value 
FROM tb_product a
CROSS JOIN (SELECT @Limit:=(SELECT max_product from tb_config))
WHERE a.active = 1
LIMIT @Limit

answered Apr 12 '18 at 13:52

xQbert

34,733
2
41
62

It's throwing a syntax error on the line `LIMIT @Limit` – celsomtrindade Apr 12 '18 at 13:58
Then I refer you to using `@rowcount`, `SET SQL_SELECT_LIMIT`, or using a prepared statement. defined in prior answers https://stackoverflow.com/questions/245180/variable-limit-clause-in-mysql – xQbert Apr 12 '18 at 15:27

score 0 · Answer 2 · answered Apr 12 '18 at 18:11

With help from @ENargit's answer in Variable LIMIT Clause in MySQL, you can do it using a row count variable.

Assuming the following schema:

CREATE TABLE
  tb_product
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10),
    `value` VARCHAR(10)
);


INSERT INTO 
  `tb_product`
(`name`, `value`)
VALUES
('Name1','Value1'),
('Name2','Value2'),
('Name3','Value3'),
('Name4','Value4'),
('Name5','Value5'),
('Name6','Value6'),
('Name7','Value7'),
('Name8','Value8'),
('Name9','Value9'),
('Name10','Value10');

CREATE TABLE 
  `tbl_config`
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  `type` VARCHAR(10),
  `value` INT
);

INSERT INTO
  `tbl_config`
   (`type`,`value`)
 VALUES
 ('something',10),
 ('maxrows',7);

You can reference the config table with a subquery:

SELECT * FROM (
  SELECT 
    tb_product.*, 
    @rownum := @rownum + 1 AS RowNum
  FROM tb_product, 
  (SELECT @rownum := 0) AS CounterTbl
) AS DataTbl 
WHERE 
  RowNum <= (
    SELECT
      `value`
    FROM
      `tbl_config`
    WHERE
      `type` = 'maxrows'
);

Gives you the first 7 rows (according to the config value). You can obviously extend this to do sorting etc.

SQLFiddle: http://www.sqlfiddle.com/#!9/f789e4/2

Mysql limit results based on value from column

2 Answers2