[FIXED] How to limit results of a LEFT JOIN

Issue

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!

Solution

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

Answered By - Thomas

Answer Checked By - Clifford M. (PHPFixing Volunteer)