Geek asks and answers

LEFT DOMESTIC ENTRANCE WITH LIMIT

I am trying to get a certain number of records from a table along with related 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

This works, but the problem is that LIMIT seems to limit the number of results, not the number of records that I find in A: (

Is there a way to force LIMIT to only take records from A into account? Since an entry from A can have many related entries in B, and I do not want to limit this

+3
source share
1 answer

Try putting a limit on a subquery and joining it.

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

Many RDBMSs will support this; I have no idea about SQLite.

+5
source

More articles:


All Articles