CODEWITHSUNDEEP
mysql
Timo002
Timo002

Reputation: 3208

MySQL limit result based on value in joined table

I have two tables, the first one contains a limit column. The number in this column must be used to limit the number of records received from the second table.

Is it possible to do this in just one query?

Below my tables and DEMO:

# Create table a
CREATE TABLE `a` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `limit` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

# Create table b
CREATE TABLE `b` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `master` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

# Fill table a
INSERT INTO `a` (`id`, `limit`)
VALUES
    (1, 3);


# Fill table b
INSERT INTO `b` (`id`, `name`, `master`)
VALUES
    (1, 'record 1', 'groupA'),
    (2, 'record 2', 'groupB'),
    (3, 'record 3', 'groupA'),
    (4, 'record 4', 'groupB'),
    (5, 'record 5', 'groupC'),
    (6, 'record 6', 'groupC'),
    (7, 'record 7', 'groupC'),
    (8, 'record 8', 'groupA'),
    (9, 'record 9', 'groupD'),
    (10, 'record 10', 'groupD');

Query I tested:

SELECT b.*
FROM b
JOIN a ON a.id = 1
GROUP BY b.master
LIMIT 3

This selects only 3 records.

But now I want the limit to be read from table a. I tried to limit like this, but that fails:

SELECT b.*
FROM b
JOIN a ON a.id = 1
GROUP BY b.master
LIMIT a.limit

EDIT: I've updated the question including the group by statement

Upvotes: 1

Views: 43

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You cannot use user-defined MySQL variables or table fields in the LIMIT clause. What you can do is use a variable to enumerate records of table b. Then use this variable to apply the limit:

SELECT t.id, t.name
FROM (
   SELECT id, name, @rn := @rn + 1 AS rn
   FROM b
   CROSS JOIN (SELECT @rn := 0) AS v
   ORDER BY id) AS t
INNER JOIN a ON a.id = 1 AND t.rn <= a.`limit`;

Demo here

Edit:

Here's a version that handles groups. It limits the records of b to those groups having the biggest population:

SELECT b.id, b.name, b.master
FROM b
INNER JOIN (
   SELECT master, @rn := @rn + 1 AS rn
   FROM b
   CROSS JOIN (SELECT @rn := 0) AS v
   GROUP BY master
   ORDER BY COUNT(*) DESC) AS t ON b.master = t.master
INNER JOIN a ON a.id = 1 AND t.rn <= a.`limit`;

Demo here

Upvotes: 2

Related Questions