CODEWITHSUNDEEP
mysql
Максим Зубков
Максим Зубков

Reputation: 635

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

Upvotes: 1

Views: 58

Answers (1)

Shekhar Joshi
Shekhar Joshi

Reputation: 1008

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

Upvotes: 3

Related Questions