MySQL Multiple Left Join Limit

Question

How would I impose a left join limit on query with multiple left joins that depend on one another? See comment below on the LIMIT:

SELECT 
    a.*,GROUP_CONCAT(c.body SEPARATOR ' ') AS bodies
FROM a   
    LEFT JOIN b ON b.id_a=a.id    
    LEFT JOIN c ON c.id=b.id_c LIMIT 5 # LIMIT 5 Here Does Not Work 
WHERE ...

Sample data set...

DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (  
  `id` int(11) NOT NULL
);

-- Contains relationship between a and c
DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
  `id_a` int(11) NOT NULL,
  `id_c` int(11) NOT NULL
);

-- Contains body contents
DROP TABLE IF EXISTS `c`;
CREATE TABLE `c` (
  `id` int(11) NOT NULL,
  `body` varchar(2000) NOT NULL
);


-- ----------------------------
-- Sample Records
-- ----------------------------
INSERT INTO `a` VALUES ('1');

INSERT INTO `b` VALUES 
('1','1'), ('1','2'), ('1','3'),
('1','4'), ('1','5'), ('1','6'),
('1','7'), ('1','8'), ('1','9'), 
('1','10');

INSERT INTO `c` VALUES
('1','aa'),('2','bb'), ('3','cc'),
('4','dd'), ('5','ee'), ('6','ff'),
('7','gg'), ('8','hh'), ('9','ii'),
('10','jj');

... and SQLFiddle at http://sqlfiddle.com/#!9/c1822/12

Also here is a subquery rewrite that I tried but doesn't work because the outer table is not accessible from the nested subquery and fails with "unknown column a.id in where clause": http://sqlfiddle.com/#!9/c1822/3

Also here is a subquery find_in_set rewrite http://sqlfiddle.com/#!9/2d43bb/1 which works but is too slow with large data sets.

Answer 1

Here is a proper way to write this using variables. Andomar's solution might work, but it suffers three issues:

• In more recent versions of MySQL, the order by needs to be in a subquery.
• More importantly, MySQL does not guarantee the order of evaluation of expressions in the select. So, you should not assign a variable in one expression and use it in another.
• The variables are not initialized in the query

So, a better version looks like:

select . . .
from table1 t1 left join
     (select t2.*,
             (@rn := if(@id = t2.t1_id, @rn + 1,
                        if(@id := t2.t1_id, 1, 1)
                       )
             ) as rn
      from (select t2.*
            table2 t2
            order by t1_id
           ) t2 cross join
           (select @rn := 0, @id := -1) params
     ) t2
     on t2.t1_id = t1.id
where t2.rn <= 5;  -- At most 5 rows

Your use of group_concat() suggests that a group by is also necessary. However, your question seems more general about limiting the rows and you don't provide much information on what you are actually trying to accomplish.

Answer 2

You can use variables to number each row in the subquery. Then you can filter out rows based on that number. This example limits the left join to at most 3 results with the same t1_id:

select  *
from    table1 t1
left join
        (
        select  @rn := case when t1_id = @prev_id then @rn + 1 else 1 end rn
        ,       @prev_id := t1_id
        ,       t2.*
        from    table2 t2
        order by
                t1_id
        ) t2rn
on      t2rn.t1_id = t1.id
        and t2rn.rn < 4  -- At most 3 rows

Example at SQL Fiddle.