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).
Expand|Select|Wrap|Line Numbers
- SELECT table1.colA , table1.colB, table1.colC, table2.colX, table2.colZ
- FROM table1
- LEFT JOIN table2 ON table1.id=table2.id
- LIMIT 0,10;
Henry