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.

wanted_order_id
detail_with_items.sql
Replay: real traced execution (multi-file project)
CREATE TABLE request_choice AS WITH params(wanted_order_id) AS (VALUES (101)) 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 (102)) 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 (103)) 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;

  1. choice ← 1 row

    1CREATE TABLE request_choice AS WITH params(wanted_order_id) AS (VALUES (101)) SELECT wanted_order_id FROM params;2CREATE TABLE orders (id INTEGER, customer TEXT, status TEXT);
    values this step1 rowchoice

  2. orders ← 3 rows

    3CREATE TABLE order_items (order_id INTEGER, item TEXT, qty INTEGER);4INSERT INTO orders VALUES (101, 'Ada', 'open'), (102, 'Lin', 'paid'), (103, 'Mira', 'open');5INSERT INTO order_items VALUES (101, 'notebook', 2), (101, 'pen', 3), (102, 'lamp', 1), (103, 'bag', 1), (103, 'tag', 4);
    values this step3 rowsorders

  3. order_items ← 5 rows

    4INSERT INTO orders VALUES (101, 'Ada', 'open'), (102, 'Lin', 'paid'), (103, 'Mira', 'open');5INSERT INTO order_items VALUES (101, 'notebook', 2), (101, 'pen', 3), (102, 'lamp', 1), (103, 'bag', 1), (103, 'tag', 4);6SELECT 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;
    values this step5 rowsorder_items

  4. result ← 2 rows

    5INSERT INTO order_items VALUES (101, 'notebook', 2), (101, 'pen', 3), (102, 'lamp', 1), (103, 'bag', 1), (103, 'tag', 4);6SELECT 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;
    values this step2 rowsresult

  1. choice ← 1 row

    1CREATE TABLE request_choice AS WITH params(wanted_order_id) AS (VALUES (102)) SELECT wanted_order_id FROM params;2CREATE TABLE orders (id INTEGER, customer TEXT, status TEXT);
    values this step1 rowchoice

  2. orders ← 3 rows

    3CREATE TABLE order_items (order_id INTEGER, item TEXT, qty INTEGER);4INSERT INTO orders VALUES (101, 'Ada', 'open'), (102, 'Lin', 'paid'), (103, 'Mira', 'open');5INSERT INTO order_items VALUES (101, 'notebook', 2), (101, 'pen', 3), (102, 'lamp', 1), (103, 'bag', 1), (103, 'tag', 4);
    values this step3 rowsorders

  3. order_items ← 5 rows

    4INSERT INTO orders VALUES (101, 'Ada', 'open'), (102, 'Lin', 'paid'), (103, 'Mira', 'open');5INSERT INTO order_items VALUES (101, 'notebook', 2), (101, 'pen', 3), (102, 'lamp', 1), (103, 'bag', 1), (103, 'tag', 4);6SELECT 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;
    values this step5 rowsorder_items

  4. result ← 1 row

    5INSERT INTO order_items VALUES (101, 'notebook', 2), (101, 'pen', 3), (102, 'lamp', 1), (103, 'bag', 1), (103, 'tag', 4);6SELECT 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;
    values this step1 rowresult

  1. choice ← 1 row

    1CREATE TABLE request_choice AS WITH params(wanted_order_id) AS (VALUES (103)) SELECT wanted_order_id FROM params;2CREATE TABLE orders (id INTEGER, customer TEXT, status TEXT);
    values this step1 rowchoice

  2. orders ← 3 rows

    3CREATE TABLE order_items (order_id INTEGER, item TEXT, qty INTEGER);4INSERT INTO orders VALUES (101, 'Ada', 'open'), (102, 'Lin', 'paid'), (103, 'Mira', 'open');5INSERT INTO order_items VALUES (101, 'notebook', 2), (101, 'pen', 3), (102, 'lamp', 1), (103, 'bag', 1), (103, 'tag', 4);
    values this step3 rowsorders

  3. order_items ← 5 rows

    4INSERT INTO orders VALUES (101, 'Ada', 'open'), (102, 'Lin', 'paid'), (103, 'Mira', 'open');5INSERT INTO order_items VALUES (101, 'notebook', 2), (101, 'pen', 3), (102, 'lamp', 1), (103, 'bag', 1), (103, 'tag', 4);6SELECT 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;
    values this step5 rowsorder_items

  4. result ← 2 rows

    5INSERT INTO order_items VALUES (101, 'notebook', 2), (101, 'pen', 3), (102, 'lamp', 1), (103, 'bag', 1), (103, 'tag', 4);6SELECT 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;
    values this step2 rowsresult
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.