A simple LEFT JOIN

Here's a join query that counts how many items each client has bought:

 SELECT clients.name AS client_name, count(item)
 FROM clients
 LEFT JOIN sales ON sales.client_id = clients.id
 GROUP BY clients.name
 ORDER BY count(item) DESC

Let's go through that query one step at a time:

1 FROM clients

id name email
1 mr darcy [email protected]
2 luna [email protected]
3 nala [email protected]
4 tigger [email protected]

2 LEFT JOIN sales

Here's the sales table:

client_id item
1 catnip
1 blanket
1 tuna
2 tuna
5 laser pointer

3 LEFT JOIN sales ON sales.client_id = clients.id

Clients data on the left, sales on the right

id name email client_id item
1 mr darcy [email protected] 1 blanket
1 mr darcy [email protected] 1 catnip
1 mr darcy [email protected] 1 tuna
2 luna [email protected] 2 tuna
3 nala [email protected]
4 tigger [email protected]

4 GROUP BY clients.name

id name email client_id item
2 luna [email protected] 2 tuna
id name email client_id item
1 mr darcy [email protected] 1 blanket
1 mr darcy [email protected] 1 catnip
1 mr darcy [email protected] 1 tuna
id name email client_id item
3 nala [email protected]
id name email client_id item
4 tigger [email protected]

5 SELECT clients.name AS client_name, count(item)

client_name count(item)
luna 1
mr darcy 3
nala 0
tigger 0

6 ORDER BY count(item) DESC

client_name count(item)
mr darcy 3
luna 1
tigger 0
nala 0