Missing entries in join

I've got a question about a query.

I've got two tables. The first table has to columns, a start-date and an end-date.

For example start: 00:00:00 and end: 00:15:00

The first table period:

|---start---|----end----|
| 00:00:00  | 00:15:00  |
| 00:15:01  | 00:30:00  |
| 00:30:01  | 00:45:00  |
| 00:45:01  | 01:00:00  |
|-----------|-----------|

The seconds table tt contains following information: name,zeit,points

|---name---|----zeit----|---points---|
|   AAAA   |  00:11:00  |     5      |
|   AAAA   |  00:12:00  |     5      |
|   BBBB   |  00:08:00  |     9      |
|   BBBB   |  00:10:00  |     9      |
|   CCCC   |  00:13:00  |     8      |
|   AAAA   |  00:31:00  |     1      |
|   BBBB   |  00:37:00  |     3      |
|   AAAA   |  00:47:00  |     7      |
|   BBBB   |  00:50:00  |     2      |
|   CCCC   |  00:55:00  |     4      |
|----------|------------|------------|

I've used the following query to summarize the points of each team grouped by the period:

SELECT
   period.end,
   IFNULL(sum(tt.punkte), 0) punkte,
   tt.name
FROM period
   LEFT JOIN tt ON (tt.zeit = period.start)
GROUP BY period.end,tt.name

With this query I get this result:

|---end----|---punkte---|----name----|
| 00:15:00 |     10     |    AAAA    |
| 00:15:00 |     18     |    BBBB    |
| 00:15:00 |      8     |    CCCC    |
| 00:45:00 |      1     |    AAAA    |
| 00:45:00 |      3     |    BBBB    |
| 01:00:00 |      7     |    AAAA    |
| 01:00:00 |      2     |    BBBB    |
| 01:00:00 |      4     |    CCCC    |
|----------|------------|------------|

As you can see the results table is missing the rows with end 00:30:00 and there are only two rows with end 00:45:00.

So I've tried everything to add these rows to the result:

| 00:30:00 |      0     |    AAAA    |
| 00:30:00 |      0     |    BBBB    |
| 00:30:00 |      0     |    CCCC    |
| 00:45:00 |      0     |    CCCC    |

How can I change the query to get these missing rows to my res

Another way, that doesn't use dynamic SQL would be to first CROSS JOIN the period with a (derived) table that holds DISTINCT name values from tt and then LEFT JOIN tt:

SELECT 
    period.end,
    n.name,
    IFNULL(SUM(tt.punkte), 0) AS punkte
FROM  
        period 
    CROSS JOIN 
        ( SELECT DISTINCT name FROM tt ) AS n 
    LEFT JOIN 
        tt 
    ON 
       tt.name = n.name 
       AND (tt.zeit = period.start) 
GROUP BY
    period.end,
    n.name ;

StackExchange Database Administrators Q#155063, answer score: 2