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'

score 0 · Answer 1 · answered Jul 22 '22 at 13:56

0

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.

answered Jul 22 '22 at 13:56

Taylor

3,942
2
20
33

yes, is a one to many relation because TimeslotInstance to TimeSlotInstancereservation – John Jul 22 '22 at 14:35
what kind of additional criteria I need in where clause? – John Jul 22 '22 at 14:50
It depends on how the relationship between these work. – Taylor Jul 22 '22 at 14:51
one TimeSlotInstance can have many TimeSlotInstanceReservation – John Jul 22 '22 at 14:58
there are a problem, if TimeSlotInstanceReservation is null, so consequently this join condition is not fulfilled "left join TimeSlotInstanceReservation as tsir on {tsi.pk} = {tsir.timeslotinstance}" how can I bypass it and get the {tsi.capacitytotal} back? – John Jul 22 '22 at 15:24
Please read the definition of a LEFT JOIN I linked to. – Taylor Jul 22 '22 at 18:06
Hi, I edited the post, I added the query, you know what is the problem? – John Jul 24 '22 at 14:59
You haven't described the problem other than saying one query doesn't work and one does. – Taylor Jul 25 '22 at 13:15
I resolve with group by – John Jul 26 '22 at 09:50

Join Table condition

1 Answers1