Connecting Multiple Tables

The Complex Report

The head of operations wanted a master list: *"Show me every order, the customer who made it, the product they bought, and the warehouse it shipped from."*

That's FOUR tables: `orders`, `customers`, `products`, and `warehouses`. I needed to chain them all together.

The Quest: The Multi-Join Chain

You can add as many `JOIN` clauses as شما need. Each one links to something that's already been established in the query.

The Implementation: The Chain

SELECT

o.order_id,

c.name AS customer_name,

p.product_name,

w.city AS warehouse_city

FROM orders o

JOIN customers c ON o.customer_id = c.id

JOIN order_items oi ON o.order_id = oi.order_id

JOIN products p ON oi.product_id = p.id

JOIN warehouses w ON o.warehouse_id = w.id;

How It Works

1. Start with `orders`.

2. Join `customers` (via `customer_id`).

3. Join `order_items` (via `order_id`).

4. Join `products` (via `product_id` in `order_items`).

5. Join `warehouses` (via `warehouse_id` in `orders`).

The "Oops" Moment

I once joined in the wrong order, linking `products` to `orders` before linking through `order_items`. The query returned no results because there was no direct key between those two tables.

**Pro Tip**: Map out the relationships on paper first. Draw the connections like a flowchart before شما write the SQL.

The Victory

The ops head had a 360-degree view of every transaction. We discovered that most delays were coming from a single warehouse, which led to a logistics overhaul.

Your Task for Today

Join three or more tables together. Draw the relationship diagram first, then write the SQL.

*Day 38: The Many-to-Many Puzzle.*