MySQL Forums
Forum List  »  Newbie

Can you do a LIMIT but only based on one table in a join?
Posted by: Doug Matthews
Date: April 02, 2022 07:23PM

Hello. I have a problem. I'm selecting from table "transactions" to get general transaction data and inner joining table "transactions_items" to get the items purchased. I'm paginating at 5 transactions per page. When I dump the 5 transactions, if the user touches one of the lines it loads the items in a side window. I've always been told to minimize roundtrips to the server so I figured I'd pull the transactions and their items in one query. Works great, but the pagination is screwed up.

So say I have 10 records in transactions and each transaction has 10 related items in the items table. So when I do SELECT fields FROM transactions t INNER JOIN transactions_items ti ON t.tid = ti.tid LIMIT 0,5 instead of getting tid 1, tid 2, tid 3, tid 4 and tid 5 I just get tid 1 because the joined rows are counting toward the pagination.

Is there a way to tell it the limit applies to results from transactions and not the general total? I assume not, but I figured before I do the extra query per record I'd ask to see.


Options: ReplyQuote

Written By
Can you do a LIMIT but only based on one table in a join?
April 02, 2022 07:23PM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.