Can you do a LIMIT but only based on one table in a join?

[Doug Matthews]

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.

Thanks!