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).

SELECT table1.colA , table1.colB, table1.colC, table2.colX, table2.colZ
FROM table1
  LEFT JOIN table2 ON table1.id=table2.id
LIMIT 0,10;
 

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:

SELECT
        `t1_master`.`colA`,
        `t1_master`.`colB`,
        `t1_master`.`colC`,
        `t2_master`.`colX`,
        `t2_master`.`colZ`
    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`)
    )