Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Limit a Left Join on the first table

Tags:

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.

UPDATE 1: OK - thanks to jviladrich - it worked. Here's the code:

SELECT gems.gemid, gems.title, r.tot, gemdetail.filename  
FROM ((gems 
INNER JOIN (SELECT gems.gemid from gems WHERE gems.grade = '7'  ORDER BY gems.gemid LIMIT 0, 10) g
ON (gems.gemid = g.gemid)
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 ;

UPDATE 2: The code from yogeshr below also works - probably the one I'll go with. Thanks to both of you!