MySQL Nested SQL with limits

Question

I know it's not possible to use limits within nested INs, but I think there is a way to do this, I'm just not sure how.

I have a table that contains both ratings and comments (simplified for explanation)

mySingleTable: 
+----+------------------+-----------+-----------+-----------------+
| id | reviewer_comment | is_rating | parent_id | reviewer_rating | 
+----+------------------+-----------+-----------+-----------------+ 
| 1  | well done rateA  | 1         |  0        | 5 Stars         | 
| 2  | commentAonRateA  | 0         |  1        |                 |
| 3  | commentBonRateA  | 0         |  1        |                 | 
| 4  | commentConRateA  | 0         |  1        |                 |
| 5  | commentDonRateA  | 0         |  1        |                 | 
| 6  | commentEonRateA  | 0         |  1        |                 | 
| 7  | commentFonRateA  | 0         |  1        |                 | 
| 8  | well done rateB  | 1         |  0        | 4 Stars         | 
| 9  | well done rateC  | 1         |  0        | 5 Stars         | 
| 11 | well done rateD  | 1         |  0        | 3 Stars         | 
| 12 | well done rateE  | 1         |  0        | 2 Stars         |
| 13 | well done rateF  | 1         |  0        | 5 Stars         | 
| 14 | well done rateG  | 1         |  0        | 3 Stars         | 
| 15 | commentAonRateD  | 0         |  11       |                 | 
+----+------------------+-----------+-----------+-----------------+ 

So,
if is_rating = 1, its a rating.
if is_rating = 0, its a comment on the rating (its parent rating is where parent_id=id)

so this would look like:

well done rateA *****  
 commentAonRateA  
 commentBonRateA  
 commentConRateA  
 commentDonRateA  
 commentEonRateA  
 commentFonRateA    
well done rateB ****  
well done rateC *****  
well done rateD ***  
 commentAonRateD    
well done rateE **  
well done rateF *****  
well done rateG ***  

What I want to do is select the newest five ratings, with the ASSOCIATED newest 5 comments, using only 1 query

So, some how join these two:

SELECT ratings.*
        FROM mySingleTable as ratings
        WHERE 
        is_rating = 1
        ORDER BY timestamp DESC LIMIT 0, 5  

    SELECT comments.*
        FROM mySingleTable as comments
        Where 
        comments.parent_id = ratings.id 
        AND is_rating = 0
        ORDER BY timestamp DESC LIMIT 0, 5

The 2nd query needs to somehow know about the ratings query

Answer 1

Please try this query which is now simplified and tested.

SELECT *
FROM
 (SELECT *,
        IF (group_id = @prev,
            @n := @n + 1,
            @n := 1 AND @prev := group_id) as position
 FROM (
   SELECT mySingleTable.*, group_id
   FROM   mySingleTable
   INNER JOIN
       (SELECT id AS group_id
        FROM   mySingleTable
        WHERE  is_rating = 1
        ORDER BY timestamp DESC LIMIT 0, 5
       ) AS top5ratings
   ON     mySingleTable.id = group_id OR mySingleTable.parent_id = group_id
   ORDER BY group_id DESC,
            mySingleTable.parent_id ASC,
            timestamp DESC) AS all_reviews
  JOIN  (SELECT @n := 0, @prev := 0) AS setup) AS reviews
WHERE reviews.position < 7

Keep in mind that SELECT * is bad practice. I used it to simplify reading.