LEFT OUTER JOIN with LIMIT

Question

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

Answer 1

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.