Joe Celko's SQL for Smarties, 3rd Edition

17.7. Packing Joins

We can relate two tables together based on quantities in each of them. The simplest example is filling customer orders from our inventories at various stores. To make life easier, let’s assume that we have only one product, we process orders in increasing customer_id order, and we draw from store inventory by increasing store_id.

CREATE TABLE Inventory
(store_id INTEGER NOT NULL PRIMARY KEY,
 item_qty INTEGER NOT NULL CHECK (item_qty >= 0));

INSERT INTO Inventory (store_id, item_qty)
VALUES (10, 2),(20, 3), (30, 2);

CREATE TABLE Orders
(customer_id CHAR(5) NOT NULL PRIMARY KEY,
 item_qty INTEGER NOT NULL CHECK (item_qty > 0));

INSERT INTO Orders (customer_id, item_qty)
VALUES ('Bill', 4), ('Fred', 2);

What we want to do is ...