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

You need to pull the join condition out of your subquery. Try:

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

Answer 2

It fails because when you put parenthesis around your query and give it the alias "tb" you have created a derived table. Your derived table has no knowledge of the tbl_product table having alias "ta"

Try using ON or WHERE outside of the derived table, then reference that table using the alias" tb" you provided

EDIT:

The use of "LIMIT" limits the results of the query in its entirety. While you have "LIMIT 10" what you actually want is 50 rows (or less if there are fewer than 5 historical), is that right?

10 products, joined to 5 historical records, returning 50 total rows.

In this case, you can have a single query solution be joining the product table to itself in a derived table having "LIMIT 10"

Such as:

SELECT *
FROM tbl_product ta
JOIN (SELECT * FROM tbl_product tz WHERE tz.product_id = ta.product_id LIMIT 10) tc
LEFT JOIN (SELECT tx.transaction_date FROM tbl_transaction tx 
    WHERE tx.product_id=ta.product_id LIMIT 5) tb

You could also us "in" and specify the top 10 such as:

SELECT *
FROM tbl_product ta
LEFT JOIN (SELECT tx.transaction_date FROM tbl_transaction tx 
WHERE tx.product_id=ta.product_id LIMIT 5) tb 
WHERE ta.product_id IN 
  (SELECT z.product_id FROM tbl_product z LIMIT 10)

Answer 3

Edit:

If you want to enforce a limit on each product I would separate it into two queries.

First get a list of 10 products and then run another query for each of those returning the last five transactions.

Answer 4

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