MySQL: How to query multiple tables and apply a LIMIT to only one?

mysql

Solution

This will get you 10 items. However, you should add a WHERE clause and ORDER BY clause to get the items you are looking for.

SELECT * FROM subitem INNER JOIN 
              (SELECT * FROM items LIMIT 10) AS I
              ON subitem.item = I.id

Problem

Suppose I have one table with items and another table with subitems. I would like to return all of the subitems associated with a limited number of items. In essence I would like to join these two queries: ``` SELECT * FROM subitem SELECT * FROM item LIMIT 10 ``` where `subitem.item = item.id`: I tried this: ``` SELECT * FROM subitem INNER JOIN item ON subitem.item = item.id LIMIT 10 ``` However, this query only returns 10 subitems (as you would expect). I would like to retrieve all the subitems while limiting only the number of items to 10. How can I achieve this?

Original source