Fulfledge

Join tables together

INPUT · Slides

Adding the members table

01 / 04

The table of who bought

There was one more number left over in orders, wasn't there. member_id.

It points at the id in the members table. One member is one record.

  • id … the member number
  • name … the member's name
  • city … the town they live in
SELECT * FROM members;

Result

1 | Minato | Aoba City
2 | Suzuna | Hinata Town
3 | Kaede | Aoba City
4 | Itsuki | Mizuumi City
5 | Nonoka | Hinata Town

02 / 04

A sketch of the three tables

That makes three tables. Put the relationships into words and they go like this.

  • orders.item_id points at items.id
  • orders.member_id points at members.id
  • items and members are not connected directly

orders sits in the middle, bridging the tables on either side. "What" and "who" are both held by the orders table.

SELECT id, item_id, member_id  FROM orders  LIMIT 3;

Result

1 | 1 | 1
2 | 3 | 2
3 | 2 | 1

03 / 04

Joining on the member name

You join it exactly as you did items. The only change is the key in the ON, which becomes orders.member_id = members.id.

Orders and members are paired up, and you can read who bought how many, by name.

SELECT members.name, orders.quantity  FROM orders  JOIN members  ON orders.member_id = members.id;

Result

Minato | 2
Suzuna | 4
Minato | 4
Kaede | 1
Itsuki | 3
Kaede | 2
Itsuki | 2
Suzuna | 3
Minato | 6

04 / 04

name collides

The thing to watch here is that both items and members have a column called name.

While you are only joining two, plain name gets through. Join three and it errors with "I cannot tell which name". Get into the habit of writing members.name from the start.

SELECT members.name,  SUM(orders.quantity) AS total  FROM orders  JOIN members  ON orders.member_id = members.id  GROUP BY members.name  ORDER BY total DESC;

Result

Minato | 12
Suzuna | 7
Itsuki | 5
Kaede | 3