To force each category to be displayed, use a reference table and a LEFT JOIN.

     
mysql> CREATE TABLE testscore
    -> (
    ->  subject INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->  age             INT UNSIGNED NOT NULL,
    ->  sex             ENUM('M','F') NOT NULL,
    ->  score   INT,
    ->  PRIMARY KEY (subject)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO testscore (age,sex,score)
    ->  VALUES
    ->  (5,'M',5),
    ->  (5,'M',4),
    ->  (5,'F',6),
    ->  (5,'F',7),
    ->  (6,'M',8),
    ->  (6,'M',9),
    ->  (6,'F',4),
    ->  (6,'F',6),
    ->  (7,'M',8),
    ->  (7,'M',6),
    ->  (7,'F',9),
    ->  (7,'F',7),
    ->  (8,'M',9),
    ->  (8,'M',6),
    ->  (8,'F',7),
    ->  (8,'F',10),
    ->  (9,'M',9),
    ->  (9,'M',7),
    ->  (9,'F',10),
    ->  (9,'F',9)
    -> ;
Query OK, 20 rows affected (0.00 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT subject, age, sex, score FROM testscore ORDER BY subject;
+---------+-----+-----+-------+
| subject | age | sex | score |
+---------+-----+-----+-------+
|       1 |   5 | M   |     5 |
|       2 |   5 | M   |     4 |
|       3 |   5 | F   |     6 |
|       4 |   5 | F   |     7 |
|       5 |   6 | M   |     8 |
|       6 |   6 | M   |     9 |
|       7 |   6 | F   |     4 |
|       8 |   6 | F   |     6 |
|       9 |   7 | M   |     8 |
|      10 |   7 | M   |     6 |
|      11 |   7 | F   |     9 |
|      12 |   7 | F   |     7 |
|      13 |   8 | M   |     9 |
|      14 |   8 | M   |     6 |
|      15 |   8 | F   |     7 |
|      16 |   8 | F   |    10 |
|      17 |   9 | M   |     9 |
|      18 |   9 | M   |     7 |
|      19 |   9 | F   |    10 |
|      20 |   9 | F   |     9 |
+---------+-----+-----+-------+
20 rows in set (0.00 sec)

mysql>
mysql> CREATE TABLE ref (score INT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO ref (score)
    -> VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT ref.score, COUNT(testscore.score) AS occurrences
    -> FROM ref LEFT JOIN testscore ON ref.score = testscore.score
    -> GROUP BY ref.score;
+-------+-------------+
| score | occurrences |
+-------+-------------+
|     0 |           0 |
|     1 |           0 |
|     2 |           0 |
|     3 |           0 |
|     4 |           2 |
|     5 |           1 |
|     6 |           4 |
|     7 |           4 |
|     8 |           2 |
|     9 |           5 |
|    10 |           2 |
+-------+-------------+
11 rows in set (0.00 sec)

mysql>
mysql> drop table testscore;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table ref;
Query OK, 0 rows affected (0.00 sec)

Related examples in the same category

1.	LEFT JOIN tables
2.	'USING' command in LEFT JOIN
3.	Two LEFT JOIN in select command
4.	Two LEFT JOIN
5.	Creating the table list with LEFT JOIN and then forming the linking connection with ON
6.	A LEFT JOIN and a regular join
7.	To list each author record, whether or not there are any book records for it, use a LEFT JOIN
8.	Creating Left Joins
9.	Replacing the ON clause with the USING clause for Left Join
10.	Use a left join to link more than two tables.
11.	Addition of the LEFT keyword to each join definition
12.	Left Joins (Left Outer Joins)
13.	Performing a LEFT JOIN on just the customer and sales tables.
14.	Table order in a LEFT JOIN is important.
15.	Left outer join syntax
16.	Left outer join then order
17.	Left outer join
18.	Left outer join with using clause
19.	Left outer join with subquery
20.	Query from left outer join
21.	To return all the sales reps who have not yet made a sale

To force each category to be displayed, use a reference table and a LEFT JOIN. : Left Join « Join « SQL / MySQL

Related examples in the same category