How to specify which table to LIMIT in a LEFT JOIN?

[henryrhenryr]

Hello!

I'm having a bit of trouble with a query. I am trying to paginate a set of results using LIMIT. Each result has a bit of 'basic' data and potentially multiple 'additional' data. I have used LEFT JOIN to get the data from table 2.

I want to limit the amount of data to 10 rows of table 1 but if there are two pieces of data on table 2 for every row on table 1, I only get 5 rows of table one (ie 10 rows in all but not what I want). A GROUP BY clause seemed to prevent the LEFT JOIN from working although did force the query to return 10 rows of table1.

How can I apply the limit specifically to table 1? Or perhaps I can use a function in the LIMIT clause to calculate the correct numbers on the fly?

Here is the SQL (I use MySQL 5).

[code=sql]
SELECT table1.colA , table1.colB, table1.colC, table2.colX, table2.colZ
FROM table1
LEFT JOIN table2 ON table1.id=table 2.id
LIMIT 0,10;
[/code]

Thanks so much. It will be really great to solve this problem!

Henry

[pbmods]

Heya, Henry.

To accomplish this, you need to create derived tables from subqueries:
[code=mysql]
SELECT
`t1_master`.`co lA`,
`t1_master`.`co lB`,
`t1_master`.`co lC`,
`t2_master`.`co lX`,
`t2_master`.`co lZ`
FROM
(
(
SELECT
*
FROM
(
SELECT
*
FROM
`table1`
LIMIT 0,10
)
AS `t1_sub`
)
AS `t1_master`
LEFT JOIN
(
SELECT
*
FROM
(
SELECT
*
FROM
`table2`
)
AS `t2_sub`
)
AS `t2_master`
USING
(`id`)
)
[/code]