By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,750 Members | 1,367 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,750 IT Pros & Developers. It's quick & easy.

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

100+
P: 103
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).

Expand|Select|Wrap|Line Numbers
  1. SELECT table1.colA , table1.colB, table1.colC, table2.colX, table2.colZ
  2. FROM table1
  3.   LEFT JOIN table2 ON table1.id=table2.id
  4. LIMIT 0,10;
  5.  
Thanks so much. It will be really great to solve this problem!

Henry
Aug 10 '07 #1
Share this Question
Share on Google+
1 Reply


pbmods
Expert 5K+
P: 5,821
Heya, Henry.

To accomplish this, you need to create derived tables from subqueries:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.         `t1_master`.`colA`,
  3.         `t1_master`.`colB`,
  4.         `t1_master`.`colC`,
  5.         `t2_master`.`colX`,
  6.         `t2_master`.`colZ`
  7.     FROM
  8.     (
  9.         (
  10.             SELECT
  11.                     *
  12.                 FROM
  13.                 (
  14.                     SELECT
  15.                             *
  16.                         FROM
  17.                             `table1`
  18.                         LIMIT 0,10
  19.                 )
  20.                     AS `t1_sub`
  21.         )
  22.             AS `t1_master`
  23.         LEFT JOIN
  24.         (
  25.             SELECT
  26.                     *
  27.                 FROM
  28.                 (
  29.                     SELECT
  30.                             *
  31.                         FROM
  32.                             `table2`
  33.                 )
  34.                     AS `t2_sub`
  35.         )
  36.             AS `t2_master`
  37.             USING
  38.                 (`id`)
  39.     )
  40.  
Aug 14 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.