MySQL Limit LEFT JOIN Subquery After Joining

Question

I currently have this query:

SELECT post.id AS postID, sCom.id as CommentID FROM `post` LEFT JOIN (SELECT * FROM `comment` LIMIT 5) AS sCom ON sCom.post_id = post.id; 

Output:

 postID | CommentID 1 | 1 2 | null 3 | null 4 | 2 5 | 3 5 | 4 5 | 5 

It works, but it provides a comment table before connecting. As a result, he selects the first 5 comments and displays it. All comments by id of 5 are ignored.

How can I rewrite a post request with a maximum of 5 comments?

Current table structure:

Message:

  CREATE TABLE IF NOT EXISTS `post` (
  `id` int (11) NOT NULL AUTO_INCREMENT,
  `feed_id` int (11) DEFAULT NULL,
  `user_id` int (11) DEFAULT NULL,
  `origin_id` int (11) DEFAULT NULL,
  `content` longtext COLLATE utf8_unicode_ci NOT NULL,
  `enabled` tinyint (1) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_5A8A6C8D51A5BC03` (` feed_id`),
  KEY `IDX_5A8A6C8DA76ED395` (` user_id`),
  KEY `IDX_5A8A6C8D56A273CC` (` origin_id`)
 ) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci AUTO_INCREMENT = 6;

A comment:

  CREATE TABLE IF NOT EXISTS `comment` (
  `id` int (11) NOT NULL AUTO_INCREMENT,
  `feed_id` int (11) DEFAULT NULL,
  `user_id` int (11) DEFAULT NULL,
  `post_id` int (11) DEFAULT NULL,
  `content` longtext COLLATE utf8_unicode_ci NOT NULL,
  `enabled` tinyint (1) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_9474526C51A5BC03` (` feed_id`),
  KEY `IDX_9474526CA76ED395` (` user_id`),
  KEY `IDX_9474526C4B89032C` (` post_id`)
 ) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci AUTO_INCREMENT = 11;

thanks

Answer 1

This will give you 5 comments for each post.

 SELECT p.*, c.* FROM Post p LEFT JOIN ( SELECT a.* FROM Comments a WHERE ( SELECT COUNT(*) FROM Comments b WHERE a.Post_ID = b.Post_ID AND a.ID <= b.ID ) <= 5 ) c ON a.ID = c.Post_ID