getting data from 2 tables with one-to-many relation using limit and offset on a 'master' table

Question

This seems simple so I'm a little embarrassed to ask, but here it is: I've got 2 tables - orders and items (of orders). One order can have one or more items and I'd like to use LIMIT to get e.g. first 10 orders, possibly using OFFSET too so, the question is: how to create a query that would do that? For example, if order 1 has 2 items, order 2: 1 item, order 3: 2 items, order 4: 1 item, and I need first 3 orders the expected result would be:

1. order 1 item 1
2. order 1 item 2,
3. order 2 item 1,
4. order 3 item 1,
5. order 3 item 2

Answer 1

Using a sub query to limit and given

MariaDB [sandbox]> select * from cat_books;
+--------+-------+
| idbook | name  |
+--------+-------+
|      1 | book1 |
|      2 | book2 |
|      3 | book3 |
|      4 | book4 |
+--------+-------+
4 rows in set (0.001 sec)

MariaDB [sandbox]> select * from books_sold;
+---------+--------+--------+
| id_sold | idbook | iduser |
+---------+--------+--------+
|       1 |      1 |      1 |
|       2 |      2 |      1 |
|       3 |      1 |      2 |
|       4 |      1 |      3 |
|       4 |      3 |      5 |
+---------+--------+--------+
5 rows in set (0.001 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> select *
    -> from
    -> (SELECT * FROM CAT_books order by idbook limit 2) cb
    -> join
    -> books_sold bs on bs.idbook = cb.idbook
    -> order by cb.idbook;
+--------+-------+---------+--------+--------+
| idbook | name  | id_sold | idbook | iduser |
+--------+-------+---------+--------+--------+
|      1 | book1 |       3 |      1 |      2 |
|      1 | book1 |       4 |      1 |      3 |
|      1 | book1 |       1 |      1 |      1 |
|      2 | book2 |       2 |      2 |      1 |
+--------+-------+---------+--------+--------+
4 rows in set (0.001 sec)