I have 2 tables in MySQL databased named order and order_item.
An order can have multiple order_items in it. So I am creating one order table and one order_item table (mapped with a foreign key to order table). Please see the SQLFiddle link and the schema below.

SQLFiddle

create table if not exists order_info(
    order_id int not null AUTO_INCREMENT,
    delivery_address varchar(100),
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id)
);

create table if not exists order_items(
    item_id int not null AUTO_INCREMENT,
    order_id int,
    name varchar(100),
    FOREIGN KEY (order_id) REFERENCES order_info(order_id),
    PRIMARY KEY (item_id)
);

INSERT order_info(order_id, delivery_address, created_at)
VALUES (1, "NYC Manhattan", '2021-12-27 12:05:32'),
 (2, "NYC Brooklyn", '2021-12-27 11:05:23'),
(3, "NYC Bronx", '2021-12-26 13:09:23');

INSERT order_items(item_id,order_id,name)
VALUES (1, 1, "Shoes"),
(2, 1, "Basketballs"),
(3, 1, "Baseballs"),
(4, 2, "Boxing gloves"),
(5, 3, "Baseball caps"),
(6, 3, "Nike T-shirts");

I am looking for responses which combine these 2 tables but with a count limit imposed on the number of items.
E.g. I am looking for the results in the format of a list with one order_item per order.
order_id, delivery_address, item_id, item.name
So my results will be like
[{1, NYC Manhattan, 1, Shoes},
{1, NYC Manhattan, 2, Basketballs}]
Suppose I want to see last n number of order items placed in NYC Bronx. I understand that it might lead to cases which contains returning partial orders. '' My questions are:

1. Is it a reasonable way to fire queries to the database? Is my database design good enough to support such queries?
2. I have tried a lot of query but can't make any query work which can impose limits on the count of rows in order_items.

CodePudding user response：

You can retrieve the orders with a one item only with the query:

select *
from (
  select
    o.*, i.item_id, i.name,
    row_number() over(partition by i.order_id order by item_id) as rn
  from order_info o
  left join order_items i on i.order_id = o.order_id
) x
where rn <= 1

Result:

 order_id  delivery_address  created_at           item_id  name           rn 
 --------- ----------------- -------------------- -------- -------------- -- 
 1         NYC Manhattan     2021-12-27 12:05:32  1        Shoes          1  
 2         NYC Brooklyn      2021-12-27 11:05:23  4        Boxing gloves  1  
 3         NYC Bronx         2021-12-26 13:09:23  5        Baseball caps  1  

See running example at DB Fiddle.

Change rn <= 1 to a bigger number if you want to increase the number of shown items per order.

CodePudding user response：

Try something like:

SELECT t1.order_id, t1.delivery_address, t2.item_id, t2.name FROM order_info AS t1
    INNER JOIN (
        SELECT item_id, name, order_id FROM order_items
            WHERE order_id=t1.order_id
            ORDER BY item_id LIMIT 1
    ) AS t2 ON t1.order_id=t2.order_id
    ORDER BY t1.order_id;

Seems misunderstood your question in my last answer.

Above is without any intentional overcomplicating things.