Limiting results to a distinct column with SQL

Question

I am attempting to get a list of items along with the order information for each item. An order always has at least 1 item associated with it. I want to limit this list to all of the items from 10 orders. As my query stands below, it gets 10 items. What is the best way to LIMIT this to 10 orders, while grabbing all of the items associated with these orders (be it 10 rows or 200 rows)?

SELECT o.*, i.*
FROM orders o, items i
WHERE i.order_id = o.id
LIMIT 0, 10

Much thanks!

Answer 1

Try using a subselect where you limit the orders first. In the outer select you then can fetch all items to these 10 orders.

SELECT o.*, i.*
FROM items i, (SELECT * FROM orders LIMIT 0, 10) o
WHERE i.order_id = o.id

Answer 2

SELECT oo.*, i.*
FROM (
  SELECT *
  FROM orders o
  LIMIT 10
  ) oo , items i
WHERE i.order_id = oo.id

Answer 3

I don't know mysql but to take a guess

SELECT o.*, i.*
FROM orders o, items i
WHERE i.order_id = o.id and 
o.id in (select o.id from orders o limit 0, 10)

Answer 4

Something like this should work:

SELECT o.*, i.*
FROM items i INNER JOIN orders o ON o.id = i.order_id
WHERE o.order_id IN (
  SELECT o.id
  FROM orders
  LIMIT 0, 10
)

Answer 5

SELECT orders1.*, items.*
FROM
(select * from orders 
LIMIT 10) AS orders1
LEFT JOIN items ON orders1.id = items.order_id;