How to limit results of a LEFT JOIN

user_403964 0
Viewed 65723

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!

subquery mysql-error-1054 left-join mysql sql
4 Answers

I found way to make LEFT JOIN with subquery, sorted and limited, its works on RDS Amazon, MariaDB v10.2 

LEFT JOIN activities last_activity ON last_activity.id = ( 
     SELECT id FROM activities 
     WHERE contester_id = contesters.id AND `type` IN ({$last_activity_types}) 
     ORDER BY id DESC LIMIT 1 
)

All others ways to make LEFT JOIN with GROUPING and SORTING didn't works for me. This example is working exception.

user_17750720
Related