$ settings --editor

$ settings --general

$ save --settings

SQL for beginners

Joining three tables #

Joins aren't limited to two tables, you can chain as many JOIN clauses as you need, one after another. This is common when the information you want is spread across more than two related tables.

In our database, orders and products aren't directly connected, there's no product_id column on orders. Instead, they're connected through order_items, which has both an order_id and a product_id. To get from orders to products, we need to join through order_items in the middle.

SELECT
    columns
FROM
    table_1
INNER JOIN
    table_2
ON
    table_1.column = table_2.column
INNER JOIN
    table_3
ON
    table_2.column = table_3.column;

Each INNER JOIN ... ON pair adds one more table to the result, matched using whatever column connects it to a table already in the query.

Example #

SELECT
    o.id AS order_id,
    o.status,
    p.name
FROM
    orders o
INNER JOIN
    order_items oi
ON
    o.id = oi.order_id
INNER JOIN
    products p
ON
    oi.product_id = p.id;

Result:

+----------+-----------+-------------------+
| order_id |  status   |        name       |
+----------+-----------+-------------------+
|    1     | Delivered |   Wireless Mouse  |
|    1     | Delivered | Bluetooth Speaker |
|    2     |  Shipped  |      Yoga Mat     |
|   .....  |   .....   |       .....       |

We first join orders to order_items on o.id = oi.order_id, then join the result to products on oi.product_id = p.id. The order of the joins matters for readability, but as long as each ON condition correctly links two tables that are already available, the result is the same.

Exercise #

Write a query joining orders, order_items, and products to return, for every order item, the order's id (aliased order_id), the product's name, and the quantity.

  • The result has an order_id column, a name column, and a quantity column.
  • The result contains one row per order item, with the correct order_id, product name, and quantity in each row.
Solution 
SELECT
    o.id AS order_id,
    p.name,
    oi.quantity
FROM
    orders o
INNER JOIN
    order_items oi
ON
    o.id = oi.order_id
INNER JOIN
    products p
ON
    oi.product_id = p.id;
Output will be displayed here