Need to load posts with last 3 comments for each post without using subqueries

lpa

Hello,

Looking for a CLEAN way to load posts with last 3 comments for each post without using subqueries. Is it possible to load posts and comments with the same query? Can I first load the posts with 1 query, the comments with second query and then combine both results? If yes, can you show an example?
Any help will be appreciated. Thanks.

Below are my script which uses subqueries. I need to get rid of subqueries because there are 20 posts per page. It means 20 queries to load comments.


$result = mysql_query("SELECT posts.id, posts.text, posts.type, posts.poster, posts.comments, users.profile_picture 
FROM posts left join users on posts.poster=users.id order by posts.id DESC limit $page, $step") or die('Error1'); 

while(list($pid, $text, $type, $poster, $comments, $profile_picture) = mysql_fetch_row($result)) {

//print post

if ($comments != '0' {

$result1 = mysql_query("SELECT comments.id,comments.poster, comments.text, users.profile_picture FROM comments LEFT JOIN users ON comments.poster=users.id where comments.parent='$pid' order by comments.id DESC limit 0,3") or die('Error2'); 

while(list($cid,$cposter, $ctext, $cprofile_picture) = mysql_fetch_row($result1)) {

//print comments

}
}

barand

The way to do it is to use SQL subqueries. But you have ruled those out (probably without knowing what they are)

barand

Here is an example which selects the top three pupil scores in each subject from a table of exam results

The select query inside the (...) is the subquery

mysql> SELECT subjectid, pupilid, pcent, rowno
    -> FROM
    ->     (
    ->     SELECT pupilid, pcent,
    ->     @row := IF(@prev=subjectid, @row+1,1) as rowno,
    ->     @prev:=subjectid as subjectid
    ->     FROM result
    ->         JOIN (SELECT @row:=0, @prev:='') as init
    ->     WHERE schoolyear=2009
    ->     ORDER BY subjectid, pcent DESC
    ->     ) as countquery
    -> WHERE rowno < 4
    -> LIMIT 9;
+-----------+---------+-------+-------+
| subjectid | pupilid | pcent | rowno |
+-----------+---------+-------+-------+
|         1 |       3 |    95 |     1 |
|         1 |      15 |    94 |     2 |
|         1 |       9 |    93 |     3 |
|         2 |       4 |    85 |     1 |
|         2 |      12 |    83 |     2 |
|         2 |      21 |    82 |     3 |
|         3 |       5 |    96 |     1 |
|         3 |       6 |    96 |     2 |
|         3 |       8 |    93 |     3 |
+-----------+---------+-------+-------+