LEFT OUTER JOIN with LIMIT

join, limit, sql, sqlite

Solution

Try putting the limit in a sub-query, and joining on that.

SELECT
  *
FROM
(
  SELECT * FROM tblA WHERE ... ORDER BY ... LIMIT 0,40
)
  AS a
LEFT JOIN
  tblB AS b
    ON a.id = b.target

Many RDBMS will support this, I have no idea about SQLite.

Problem

I'm trying to get a certain number of records from a table along with their associated data from another table: ``` SELECT a.*, b.* FROM tblA a LEFT OUTER JOIN tblB b ON a.id = b.target WHERE ... ORDER BY ... LIMIT 0,40 ``` It works, but the problem is that LIMIT seems to limit the number of results and not the number of records I find in A :( Is there any way to get LIMIT to take into account only the records from A? Because a record from A may have many related records in B and I don't want to limit that

Original source