联接表条件

Question

我有一个带有列(code、store、slotAvailable(的查询，其中将显示结果。至于列"；slotAvailable"；，它将包含用于该特定"时隙"的空闲时隙的数目；商店"；。现在，为了计算有多少空闲槽，我只能在与表进行联接的情况下这样做"TimeSlotInstanceReservation"；因为在该表中有一个字段"0"；slotConsumed"；我需要用表"的capacitytotal字段执行减法；TimeSlotInstance"：

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

然而，当我没有对给定TimeSlotInstance的预订时，在列"中没有显示结果；slotAvailable"；。我怎么能在查询中写一个条件，说"；如果没有预订，那么给我这个值，否则加入预订"；？我希望我已经很好地解释了自己。

此查询失败，但未显示错误：

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'

但这项工作：

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

您想要一个LEFT JOIN！

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

两个表之间的LEFT JOIN返回左侧表中的所有记录，再加上右侧表中符合联接条件的任何记录。如果右表中没有与联接条件匹配的记录，则会为这些列返回null，但会填充左表中的列。

因此，您的查询看起来像

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

注意，如果TimeSlotInstance->TimeSlotInstanceReservation关系是一对多关系。您需要在WHERE子句中使用其他条件来排除重复实例。如果是，请注意，您的条件必须允许TimeSlotInstanceReservation表中的NULL值，否则将从TimeSlotInstance中排除不匹配的记录。