How to limit results of a LEFT JOIN

Question

Take the case of two tables: tbl_product and tbl_transaction.
tbl_product lists product details including names and ids while tbl_transaction lists transactions involving the products and includes dates, product-ids, customers etc.

I need to display a web-page showing 10 products and for each product, the last 5 transactions. So far, no LEFT JOIN query seems to work and the subquery below would have worked if mysql could allow the tx.product_id=ta.product_id part (fails with Unknown column 'ta.product_id' in 'where clause': [ERROR:1054]).

SELECT   ta.product_id,   ta.product_name,   tb.transaction_date   FROM tbl_product ta   LEFT JOIN (SELECT tx.transaction_date FROM tbl_transaction tx WHERE tx.product_id=ta.product_id LIMIT 5) tb LIMIT 10 

Is there a way to achieve the listing I need without using multiple queries in a loop?

Edit:
This is exactly what I need from MySQL:

SELECT ta.product_id, ta.product_name, tb.transaction_date ...   FROM tbl_product ta   LEFT JOIN tbl_transaction tb ON (tb.product_id=ta.product_id LIMIT 5)   LIMIT 10 

Of course this is illegal, but I really wish it wasn't!

Answer 1

This is where ranking functions would be very useful. Unfortunately, MySQL does not yet support them. Instead, you can try something like the following.

Select ta.product_id, ta.product_name     , tb.transaction_date From tbl_product As ta     Left Join   (                 Select tx1.product_id, tx1.transaction_id, tx1.transaction_date                     , (Select Count(*)                         From tbl_transaction As tx2                         Where tx2.product_id = tx1.product_id                             And tx2.transaction_id < tx1.transaction_id) As [Rank]                 From tbl_transaction As tx1                 ) as tb         On tb.product_id = ta.product_id             And tb.[rank] <= 4 Limit 10