Reading the orders table

01 / 05

There is more than one table

Up to now you have always been looking at a single table. But the data in a real app is split across several tables.

This chapter takes a little handmade-goods shop and moves between three tables: orders, items and members. By the end you will be able to join tables together into one result.

02 / 05

The orders table

First up is the orders table. One order is one record.

id … the order number
item_id … what they bought
member_id … who bought it
quantity … how many
channel … where the order came from

SELECT * FROM orders;

Result

1 | 1 | 1 | 2 | app
2 | 3 | 2 | 4 | web
3 | 2 | 1 | 4 | shop
4 | 4 | 3 | 1 | app
5 | 5 | 4 | 3 | web
6 | 1 | 3 | 2 | web
7 | 3 | 4 | 2 | app
8 | 2 | 2 | 3 | app
9 | 5 | 1 | 6 | shop

03 / 05

Columns holding nothing but numbers

Look closely at item_id and member_id and there is nothing but numbers in them. Neither the item names nor the member names are in this table.

So the most you can read right now is "four of item 3 sold". The names are kept in other tables. Joining those up is the goal of this chapter.

SELECT * FROM orders  WHERE channel = 'shop';

Result

3 | 2 | 1 | 4 | shop
9 | 5 | 1 | 6 | shop

04 / 05

The tools you have still work

Change the table and WHERE, ORDER BY and LIMIT are used exactly as before. Pick the columns, narrow the rows, sort them, decide how many — the shapes you learned carry straight over.

SELECT id, quantity FROM orders  ORDER BY quantity DESC  LIMIT 3;

Result

9 | 6
2 | 4
3 | 4

05 / 05

Aggregating works the same too

GROUP BY is no different. Total the quantity per channel and you can see where the orders are really moving.

This section is revision only — nothing new to write, so warm your hands up before moving on. Let us write some.

SELECT channel, SUM(quantity)  FROM orders  GROUP BY channel;

Result

app | 8
shop | 10
web | 9