473,322 Members | 1,781 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

103 100+
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
1 22432
pbmods
5,821 Expert 4TB
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

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

Similar topics

1
by: Stephen Patten | last post by:
Hi All, While in the process of building my table (40 or so Insert statments) can I then query ("select * from @Table_variable") and use the results up to theat point for another insert into...
1
by: Andrew | last post by:
Hello, Rather complicated one im afraid.. (well for me anyway!) Background on this one is that I have inherited a system that needs some modification, have some basic sql knowledge but fear i've...
2
by: elein | last post by:
Yes, I vacuumed. Reproduced on both 7.3.2 and 7.5. Brain dead java beans want order by clauses in views that they use. my view is: select .... from bigtable b left join lookuptable l order...
1
by: aaron.reese | last post by:
Guys, this is my problem:- I have records which are linked by two fields on a left inner join (there may be 0,1 or more records in the right hand table) The relationship is not unique (it's...
6
by: dmonroe | last post by:
hi group -- Im having a nested inner join problem with an Access SQl statement/Query design. Im running the query from ASP and not usng the access interface at all. Here's the tables: ...
3
by: angelnjj | last post by:
I'm going to do my best to describe purpose and what I need...here goes. I'm writing a data entry "quality" report and working. I'm trying to identify anytime a user adds a new contact to the db...
4
denjermen
by: denjermen | last post by:
If anybody can help me with this I will be forever grateful. I'm no programmer nor SQL expert, but weekly I have to run a report compiling data from several tables into an excel spreadsheet. I...
5
by: perryche | last post by:
I am not sure if I am asking the right question in the subject here. But, here is my problem. I have 5tables/queries with various data like below. Table1: CustomerID1, Field1, Field2 Table2:...
0
by: hinksta | last post by:
I have a query that has two tables with a join and works fine, the tables are from a vbulletin database and the query is from an old unsupported mod. I’m trying to add a third table to the query,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.