Application Query Patterns

Detail Pages

Load Parent and Rows

A detail view often combines one parent record with related child rows in a single query result.

Program

Play the script to choose an order and load the line items shown on its detail page.

detail_with_items.sql 
CREATE TABLE request_choice AS WITH params(wanted_order_id) AS (VALUES ()) SELECT wanted_order_id FROM params;
CREATE TABLE orders (id INTEGER, customer TEXT, status TEXT);
CREATE TABLE order_items (order_id INTEGER, item TEXT, qty INTEGER);
INSERT INTO orders VALUES (101, 'Ada', 'open'), (102, 'Lin', 'paid'), (103, 'Mira', 'open');
INSERT INTO order_items VALUES (101, 'notebook', 2), (101, 'pen', 3), (102, 'lamp', 1), (103, 'bag', 1), (103, 'tag', 4);
SELECT orders.id, orders.customer, orders.status, order_items.item, order_items.qty FROM orders JOIN order_items ON order_items.order_id = orders.id WHERE orders.id = (SELECT wanted_order_id FROM request_choice) ORDER BY order_items.item;

CREATE TABLE request_choice AS WITH params(wanted_order_id) AS (VALUES ()) SELECT wanted_order_id FROM params;
CREATE TABLE orders (id INTEGER, customer TEXT, status TEXT);
CREATE TABLE order_items (order_id INTEGER, item TEXT, qty INTEGER);
INSERT INTO orders VALUES (101, 'Ada', 'open'), (102, 'Lin', 'paid'), (103, 'Mira', 'open');
INSERT INTO order_items VALUES (101, 'notebook', 2), (101, 'pen', 3), (102, 'lamp', 1), (103, 'bag', 1), (103, 'tag', 4);
SELECT orders.id, orders.customer, orders.status, order_items.item, order_items.qty FROM orders JOIN order_items ON order_items.order_id = orders.id WHERE orders.id = (SELECT wanted_order_id FROM request_choice) ORDER BY order_items.item;

CREATE TABLE request_choice AS WITH params(wanted_order_id) AS (VALUES ()) SELECT wanted_order_id FROM params;
CREATE TABLE orders (id INTEGER, customer TEXT, status TEXT);
CREATE TABLE order_items (order_id INTEGER, item TEXT, qty INTEGER);
INSERT INTO orders VALUES (101, 'Ada', 'open'), (102, 'Lin', 'paid'), (103, 'Mira', 'open');
INSERT INTO order_items VALUES (101, 'notebook', 2), (101, 'pen', 3), (102, 'lamp', 1), (103, 'bag', 1), (103, 'tag', 4);
SELECT orders.id, orders.customer, orders.status, order_items.item, order_items.qty FROM orders JOIN order_items ON order_items.order_id = orders.id WHERE orders.id = (SELECT wanted_order_id FROM request_choice) ORDER BY order_items.item;
parent row `orders` supplies the detail header fields.
child rows `order_items` supplies the repeated line-item rows.
JOIN The join keeps each line item attached to its order.