Join Table condition

Question

I have a query with columns (code, store, slotAvailable) in which results will be shown. As for the column "slotAvailable", it will contain the number of free slots for that particular "store". Now, to calculate how many free slots there are, I can only do this if I do a join with the table "TimeSlotInstanceReservation" because in this table there are a field "slotConsumed" that I need to perform the subtraction with the capacitytotal field of table "TimeSlotInstance" :

TimeSlotInstance as tsi
join TimeSlotInstanceReservation as tsir on {tsir. timeSlotInstance} = {tsi.pk}

However, when I do not have a reservation on a given TimeSlotInstance, no result is shown in the column "slotAvailable". How can I write a condition in the query that says "if there is no reservation then give me this value otherwise join with the reservation"? I hope I have explained myself well.

this query failed but not show error:

SELECT 
{a.code} as 'Code',
{a.name} as 'Name',
   
   ({{

        SELECT 
      
             {tsi.capacity} - IFNULL({tsir.slotsConsumedCount},0) as slot1820
        FROM 
              {
               TimeSlotInstance as tsi 
               
               join TimeSlotHour as tsh on {tsi.timeSlotHour} = {tsh.pk}
               
               join StoreManager as sm on {sm.pk} = {tsi.store}
               
               left Join TimeSlotInstanceReservation as tsir on {tsi.pk} = {tsir.timeslotinstance}
               } 
          where  {tsh.starttimelabel} = '18:00' and {tsh.endtimelabel} = '20:00' and {tsi.day} = '2022-07-26T22:00'
    }}) as 'SLOT Available 18/20'

FROM
{
 TimeSlotInstance as tsi 
 
 join TimeSlotHour as tsh on {tsi.timeSlotHour} = {tsh.pk}
 } 
 where  {tsi.day} = '2022-07-26T22:00'

but this work:

select
    {tsi.capacity} - IFNULL({tsir.slotsConsumedCount}, 0)  as SLOT1820
        
 from
     {
       TimeSlotInstance as tsi 
       join TimeSlotHour as tsh on {tsi.timeSlotHour} = {tsh.pk}
       left join TimeSlotInstanceReservation as tsir on {tsi.pk} = {tsir.timeslotinstance}
    
     }
     
     where {tsh.starttimelabel} = '18:00' and {tsh.endtimelabel} = '20:00' AND {tsi.day} = '2022-07-26T22:00'

Answer 1

You want a LEFT JOIN!

https://www.w3schools.com/sql/sql_join_left.asp

A LEFT JOIN between two tables returns all the records from the table on the left, plus any record from the table on the right that match the join condition. In the case where no record from the right table matches the join condition, nulls are returned for those columns, though the columns from the left table will be populated.

Hence your query would look something like

SELECT tsi.code, tsi.store, 
    tsi.capacitytotal - IFNULL(tsir.slotConsumed, 0) as slotAvailable
FROM TimeSlotInstance tsi 
    LEFT JOIN TimeSlotInstanceReservation tsir on tsi.pk = tsir. timeSlotInstance

Note that if the TimeSlotInstance -> TimeSlotInstanceReservation relationship is one-to-many you will need additional criteria in a WHERE clause to exclude dupe instances. If so, be aware that your conditions must allow for NULL values in the TimeSlotInstanceReservation table or you will exclude the unmatched records from TimeSlotInstance.