Limit a Left Join on the first table

Question

I have two tables: gems and gemdetail that are left joined. I am trying to limit the LEFT JOIN to 10 records in the gems table. There are 2 other tables joined (gemreply and users) as well, but they do not contribute to the problem. The following does not work:

SELECT
    gems.gemid,
    gems.title,
    r.tot,
    gemdetail.filename
FROM
    (
        (
            gems
        LEFT JOIN(
            SELECT
                gemid,
                COUNT(*) AS tot
            FROM
                gemreply
            GROUP BY
                gemid
        ) AS r
    ON
        gems.gemid = r.gemid
        )
    LEFT JOIN gemdetail ON gems.gemid = gemdetail.gemid
    )
LEFT JOIN users ON gems.userid = users.userid
WHERE
    gems.grade = '7'
ORDER BY
    gems.gemid
LIMIT 0, 10;

This limits the total number of returned rows to 10, but since there are multiple detail records for each gem, I am left with fewer than 10 gem records. I've read every "LIMIT" post, but haven't found this situation.

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'.

Answer 1

Something like that

   SELECT * FROM A
      INNER JOIN ( SELECT * FROM A WHERE A.FIELD1='X' ORDER BY A.FIELD2 LIMIT 10) X
             ON (A.KEYFIELD=X.KEYFIELD)
      LEFT JOIN B ON (A.FIELD = B.FIELD)
      LEFT JOIN C ON (A.FIELD = C.FIELD)