Lesson 4 of 10

Joining three tables

13 min
5 checks
3 hints
Go to task

Goal

Two JOINs in a row

Explanation

One order can contain several products, and one product can be sold in several orders. This is called a many-to-many relationship, and it's stored in a junction table: each row in order_items is one line item with an order_id, a product_id and a quantity, qty.

To get from an order to the names of its products, you go through the junction table. Each JOIN adds one table with its own ON condition, and you read the chain of joins from top to bottom.

The id column exists in all three tables, so if you write it without its table name, the query stops with the error ambiguous column name. Put the table name before every column, and use AS to give same-named columns distinct names, like products.name AS product.

Your task

For each line item, show placed_on from the orders table, the product's name in a column called product, and the quantity qty. Sort the result by date.

Checks

5 checks

Columns are placed_on, product and qty
One row per line item
The product column is a name, not a number
Every line item has the right date, product and quantity
Sorted by date

Tables in this lesson

CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total       INTEGER NOT NULL,
  placed_on   TEXT    NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Layan',  'Riyadh'),
  (2, 'Khalid', 'Jeddah'),
  (3, 'Reem',   'Dammam'),
  (4, 'Fahd',   'Riyadh');

INSERT INTO orders (customer_id, total, placed_on) VALUES
  (1, 120, '2026-09-01'),
  (1,  80, '2026-09-12'),
  (2, 300, '2026-09-03'),
  (3,  45, '2026-09-20'),
  (2,  60, '2026-09-25');

INSERT INTO customers (id, name, city) VALUES (5, 'Sara', 'Jeddah');

CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  name  TEXT    NOT NULL,
  price INTEGER NOT NULL
);

CREATE TABLE order_items (
  id         INTEGER PRIMARY KEY,
  order_id   INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  qty        INTEGER NOT NULL
);

INSERT INTO products (id, name, price) VALUES
  (1, 'Notebook',    15),
  (2, 'Pen',          5),
  (3, 'Bag',         90),
  (4, 'Calculator',  60),
  (5, 'Lamp',       100);

INSERT INTO order_items (order_id, product_id, qty) VALUES
  (1, 3, 1), (1, 1, 2),
  (2, 4, 1), (2, 2, 4),
  (3, 3, 2), (3, 4, 2),
  (4, 1, 3),
  (5, 1, 2), (5, 2, 6);

Joining three tables

Explanation

Your task

Checks

Hints