在第一个表上限制左联接 [英] Limit a Left Join on the first table

问题描述

我有两个表:gem和gemdetail保持连接状态.我试图将LEFT JOIN限制为gems表中的10条记录.也有两个其他表联接(gemreply和用户)，但它们不会导致该问题.以下内容不起作用:

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;

这将返回的行总数限制为10，但是由于每个gem有多个明细记录，所以我剩下不到10个gem记录.我已经阅读了所有"LIMIT"个帖子，但没有发现这种情况.

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.

更新1: 好的-感谢jviladrich-它奏效了.这是代码:

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 ;

更新2: 下面来自yogeshr的代码也可以使用-可能是我将要使用的代码.多亏你们两个！

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

推荐答案

类似的东西

   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)

这篇关于在第一个表上限制左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！