Including rows from base table with left joins when these joins are 'empty' I've got a query which counts the number of reservations in a table over several 15minutes intervals retrieved from an other table:

SELECT 
   t.*, count(r.id) as nof_reservations
FROM (
   SELECT time_start, time_end FROM `interval` 
   WHERE time_start &lt; '18:00:00'
   AND time_end &gt; '10:00:00'
) t -- holds a row per 15m interval

-- reservations of the assets
LEFT JOIN reservations r  
ON   start_utc &lt; concat('2017-04-02 ', t.time_end)
AND  end_utc &gt; concat('2017-04-02 ', t.time_start)

-- we need to filter the assets on their type, which is 
-- stored in the assets table
LEFT JOIN assets b 
ON r.asset_id = b.id

WHERE r.deleted_at is null
AND b.asset_type_id = 6 -- asset type we need
GROUP BY t.time_start, t.time_end;


This produces the desired table. But when there are no reservations (nof_reservations=0) the whole row disappears:

| time_start | time_end | nof_reservations |
+------------+----------+------------------+
| 11:00:00   | 11:15:00 | 2                |
| 11:15:00   | 11:30:00 | 2                |
| 11:30:00   | 11:45:00 | 2                |
...
+------------+----------+------------------+


As I'm retrieving every interval &lt;1800 >1000 I would have thought to get lines like

| time_start | time_end | nof_reservations |
+------------+----------+------------------+
| 10:00:00   | 10:15:00 | 0                |
| 10:15:00   | 10:30:00 | 0                |
| 10:30:00   | 10:45:00 | 0                |
| 10:45:00   | 11:00:00 | 0                |
| 11:00:00   | 11:15:00 | 2                |
| 11:15:00   | 11:30:00 | 2                |
| 11:30:00   | 11:45:00 | 2                |
...
+------------+----------+------------------+


What am I missing in the before mentioned query?

Schemas:

CREATE TABLE `reservations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `asset_id` int(11) NOT NULL,
  `start_utc` datetime NOT NULL,
  `end_utc` datetime NOT NULL
  PRIMARY KEY (`id`),
  KEY `idx_van_utc` (`start_utc`),
  KEY `id_tot_utc` (`end_utc`),
  KEY `idx_boot_id` (`asset_id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `assets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `asset_type_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `interval` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `time_start` time NOT NULL,
 `time_end` time NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=97 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
As a rule of thumb -
when having ... LEFT JOIN t ON ..., conditions on t should be in the ON clause and not in the WHERE clause.

SELECT 
   t.*, count(b.id) as nof_reservations
FROM (
   SELECT time_start, time_end FROM `interval` 
   WHERE time_start &lt; '18:00:00'
   AND time_end &gt; '10:00:00'
) t -- holds a row per 15m interval

-- reservations of the assets
LEFT JOIN reservations r  
ON   start_utc &lt; concat('2017-04-02 ', t.time_end)
AND  end_utc &gt; concat('2017-04-02 ', t.time_start)
AND  r.deleted_at is null

-- we need to filter the assets on their type, which is 
-- stored in the assets table
LEFT JOIN assets b 
ON r.asset_id = b.id
AND b.asset_type_id = 6 -- asset type we need

GROUP BY t.time_start, t.time_end;




It's possible you want a slightly different join instead (the rest of the query, SELECT ... FROM ... t and GROUP BY stays the same as above):

-- reservations of the assets
-- and filter the assets on their type
LEFT JOIN reservations r  
    JOIN assets b 
    ON r.asset_id = b.id
    AND b.asset_type_id = 6 -- asset type we need
ON   start_utc &lt; concat('2017-04-02 ', t.time_end)
AND  end_utc &gt; concat('2017-04-02 ', t.time_start)
AND  r.deleted_at is null