Максим Зубков Максим Зубков - 7 months ago 20
SQL Question

mysql left join with limit 0,2

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

Answer

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.

related posts:

GROUP_CONCAT with limit

Mysql group_concat limit rows in grouping