Including rows from base table with left joins when these joins are 'empty'
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