Seeing members through joins and subqueries

01 / 05

A member spans two tables

To answer "who buys the most?" you need both members (who they are) and orders (what they bought).

Neither on its own gives you a picture of a person. Join on the number, then gather is the pattern of this section.

02 / 05

Join, then gather

The table a join produces gathers just like any other. GROUP BY m.id is per member, GROUP BY m.area is per area.

You gather by id rather than by name so that two members with the same name never get mixed together.

SELECT m.name, COUNT(*) AS times  FROM members m  JOIN orders o    ON m.id = o.member_id  GROUP BY m.id  ORDER BY m.id LIMIT 3;

Result

Aoi | 4
Haruto | 1
Minato | 1

03 / 05

A join makes some rows disappear

JOIN keeps only the rows present on both sides. So a member who has never bought anything drops out of the result the moment you join.

When you want "the people who have not bought yet", make the list of buyers' numbers with a subquery and look for the people not in it.

SELECT id, name  FROM members  WHERE id NOT IN (    SELECT member_id FROM orders  )  ORDER BY id;

Result

5 | Sora
10 | Fuka

04 / 05

Make the yardstick with a subquery too

Subqueries also come out for things like "the members older than average", where you work out one yardstick first and then compare.

The brackets are worked out first and become a plain number. So you can put them straight to the right of a >.

SELECT name, age  FROM members  WHERE age > (    SELECT AVG(age) FROM members  )  ORDER BY age DESC;

Result

Tsumugi | 52
Nagi | 45
Riku | 41
Fuka | 38
Shion | 36
Hinata | 34

05 / 05

Joining three tables

To get as far as money you need members, orders and items. You may write JOIN as many times over as you like.

The order you join in does not change the result. As long as you do not get which column matches which wrong, you are fine. Let us write some.

SELECT m.name,       SUM(o.quantity * i.price)         AS amount  FROM members m  JOIN orders o    ON m.id = o.member_id  JOIN items i    ON o.item_id = i.id  GROUP BY m.id  ORDER BY m.id LIMIT 3;

Result

Aoi | 5900
Haruto | 2000
Minato | 2000