How extract record that not exsist

Question

I have a query that receive two date as input ("dateFrom" and "dateTo"), now this query retur only TimeSlotInstance that have an order. Exsample : I have only monday and tuesday that have an order, the rest of TimeSlotInstance not exsisting but I want show also these day (with value set to 0), How I do this? Thanks a lot.

SELECT {t.store} as store, 
       {t.day} as day, 
       count({r.pk}) as orderCount, 
       sum({t.capacity}) as totalCapacity
FROM 
      {
       TimeSlotInstance as t  
       join TimeSlotInstanceReservation as r on {t.pk} = {r.timeSlotInstance}
       } 
 WHERE  
   {t.day} >= '2021-11-27' and {t.day} <= '2022-01-01'  and 
   {r.outcome} in 
({{ SELECT {ro.pk}  
    from {ReservationOutcome as ro} 
    WHERE {ro.code} = 'BOOKED' }}) and {t.store} in 
                   ('8796093140522') GROUP BY {t.store}, {t.day} ORDER BY {t.day} asc

I want to see this whole fault going from date 27 november 2021 to date 01 january 2022 also the day where order not exsist.

Answer 1

If you have TimeSlotInstances for each day, but the TimeSlotInstanceReservation may not exist -> use LEFT JOIN to return all TimeSlotInstances but with r.* as null where TimeSlotInstanceReservations do not exist