Greetings.

There is a bd of a type:

Parent:

news

id text 1 a 2 e 3 p

Child:

Comments:

 id text news_id 1 yo 1 ...

I need to get 10 news items and for each of them three comments:

My options are:

1) Request in cycle - no

2) Request for comments of type IN (news_id) but then LIMIT will be used for all comments, and I need for each news

It is understood that the first request pulls out 10 news, as one request to get 3 comments for each news, thank you.

    2 answers 2

    If you really want to do it with one request, here’s one fairly obvious option:

     SELECT * FROM `news` LEFT JOIN `posts` ON `news`.`id`=`posts`.`news_id` AND `posts`.`id`<=IFNULL(( SELECT `id` FROM `posts` WHERE `posts`.`news_id`=`news`.`id` ORDER BY `posts`.`id` LIMIT 2,1 ), ~0) WHERE `news`.`id`<=IFNULL(( SELECT `id` FROM `news` ORDER BY `id` LIMIT 9,1 ), ~0);

    ~ 0 is the maximum integer. As you can see, the correlated subquery is not very good, maybe it can be rewritten.

      I suspect that it is easier, and perhaps even faster (if you do not reconnect to the database every time), it will make one request for each news item. Those. pull out ten news and then for each of them make one request for comments.

      Faster, it can be due to the fact that the optimizer is easier to optimize simple queries.