Fulfledge

Join tables together

INPUT · Slides

Joining three tables

01 / 04

You can write JOIN again

To get "who bought what" into one table, you need orders joined to both items and members.

It is easy: add one more JOIN … ON …. Think of them sticking on one after another, top to bottom.

SELECT orders.id, items.name,  members.name  FROM orders  JOIN items  ON orders.item_id = items.id  JOIN members  ON orders.member_id = members.id  ORDER BY orders.id;

Result

1 | cypress chopstick rest | Minato
2 | cotton dishcloth | Suzuna
3 | sunrise mug | Minato
4 | dappled light lamp | Kaede
5 | bean coaster | Itsuki
6 | cypress chopstick rest | Kaede
7 | cotton dishcloth | Itsuki
8 | sunrise mug | Suzuna
9 | bean coaster | Minato

02 / 04

Two headings with the same name

In that result, the second and third headings are both name. There is no telling the item from the buyer.

This is where AS earns its keep. Give them aliases that say which name is which.

SELECT items.name AS item,  members.name AS buyer,  orders.quantity  FROM orders  JOIN items  ON orders.item_id = items.id  JOIN members  ON orders.member_id = members.id  ORDER BY orders.id;

Result

cypress chopstick rest | Minato | 2
cotton dishcloth | Suzuna | 4
sunrise mug | Minato | 4
dappled light lamp | Kaede | 1
bean coaster | Itsuki | 3
cypress chopstick rest | Kaede | 2
cotton dishcloth | Itsuki | 2
sunrise mug | Suzuna | 3
bean coaster | Minato | 6

03 / 04

Leave the table name off and it errors

Join three and two of the tables have a name. So writing plain name leaves it undecidable, and it errors.

ambiguous column name: name means "name is ambiguous". Always put the table name on once you join and it never happens.

SELECT name FROM orders  JOIN items  ON orders.item_id = items.id  JOIN members  ON orders.member_id = members.id;

Result

ambiguous column name: name

04 / 04

Aggregating a three-table result

A joined result is one table. So GROUP BY works just as it did, even after three joins.

Below is "takings per town". The price is in items, the quantity in orders and the town in members — an answer you can only get with all three tables in place. Let us write some.

SELECT members.city,  SUM(items.price * orders.quantity)    AS sales  FROM orders  JOIN items  ON orders.item_id = items.id  JOIN members  ON orders.member_id = members.id  GROUP BY members.city  ORDER BY sales DESC;

Result

Aoba City | 13680
Hinata Town | 6160
Mizuumi City | 1460