mysql left join with limit 0,2

Question

comment table

+------+----------+
| id   | comment  | 
+------+----------+
|    1 | foo      |
|    2 | bar      |
|    3 | foobar   |
+------+----------+

reply table

+------+----------+------------+
| id   | reply   |comment_id  |
+------+----------+------------+
|    1 | nice lol | 1          |
|    2 | ok ok    | 2          |
|    3 | hello    | 1          |
|    4 | hello2   | 1          |
|    5 | hello1   | 1          |
+------+----------+------------+



 SELECT
`comment`.`comment`,
`x`.`reply`
FROM `comment` LEFT JOIN
(SELECT GROUP_CONCAT(`reply`) as reply ,reply.commnt_id FROM `reply` 
GROUP BY   `reply`.`comment_id` ORDER BY `reply`.`id` LIMIT 0,1)x ON x.comment_id =   comment.id

the result will be

+----------+-----------------+  
| comment  | reply          |  
+----------+-----------------+ 
|    foo   | nice lol,hello  | 
|    bar   | NULL            | 
|    off   | null            | 
+------+---------------------+

the question why the second comment have null but if i make limit 0,4 its will show it

It might be worth pointing out that the correct spelling is 'reply' not 'replay'. If you have a team, they will thank you for fixing this! — Arth, Apr 20 '16 at 10:56

score 3 · Accepted Answer · edited May 23 '17 at 12:24

Mysql does not support limits in group by clause. To achieve this type of feature we can hack group_concat as shown below:

SELECT
comment.comment,
x.replay
FROM comment LEFT JOIN
 (SELECT 
    REPLACE(substring_index(group_concat(replay SEPARATOR '@@'), '@@', 2), '@@', ',') as replay ,replay.commnt_id 
  FROM replay 
  GROUP BY  replay.comment_id ORDER BY replay.id LIMIT 0,1)x 
ON x.comment_id =   comment.id

this is considering that your replies will not have '@@' in them.

mysql left join with limit 0,2

1 Answers1