当至少存在一条不符合条件的记录时，不会返回任何记录

Question

首先，我知道这个查询有点混乱，但它可以正常工作，直到至少有一条ticket记录存在，并且cancelled设置为true，此时没有返回任何记录。如果有另一条记录的已取消设置为false或两者的组合，则它可以正常工作，即返回的记录的所有票证总数都设置为0或适当的值。

我已将查询更新为where。。。t、 cancelled=0或t.cancelled为NULL，并将票证表联接更改为left（也尝试了full outer），但它仍然存在上述问题。

为什么会发生这种情况，我能做些什么来防止它？

select Title, PerformanceStarts, sum(sold) as sold, sum(sold * value) as revenue, idperformance, DoorsOpening, onsale, active, showactive, venueid,
    (
        select sum(Quantity) from allocations
        where IdAllocation in (select allocationid from PerformancePriceBands where PerformanceID = idperformance) and OnGeneralSale = 1) as alloc, 
            (
                select count(totaltickets) from
                    ( 
                        select count(t.idticket) as totaltickets from tickets t
                        where t.PerformancePriceBandID IN
                            (
                                select idperformancepriceband from PerformancePriceBands
                                where performanceid = IdPerformance) and (t.Cancelled = 0 or t.Cancelled IS NULL)
                                group by t.BookingID
                            ) as q
                where totaltickets > 7
            ) as largegroups,
            (
                select count(totaltickets) from
                    ( 
                        select count(t.idticket) as totaltickets from tickets t
                        where t.PerformancePriceBandID IN
                            (
                                select idperformancepriceband from PerformancePriceBands
                                where performanceid = IdPerformance) and (t.Cancelled = 0 or t.Cancelled IS NULL)
                                group by t.BookingID
                            ) as q
                where totaltickets between 5 and 7
            ) as mediumgroups
    from
        (
            select p.idperformance, s.title, p.PerformanceStarts, count(t.idticket) as sold, ppb.Value, p.DoorsOpening, p.OnSale, p.Active, s.active as showactive, p.VenueID
            from shows s
            join performances p on p.ShowID = s.IdShow
            join PerformancePriceBands ppb on ppb.PerformanceID = p.IdPerformance
            left join tickets t on t.PerformancePriceBandID = ppb.IdPerformancePriceBand
            where p.IdPerformance = 23206 and (t.Cancelled = 0 or t.Cancelled is null)
            group by s.title, p.performancestarts, ppb.Value, p.idperformance, p.DoorsOpening, p.onsale, p.active, s.active, p.VenueID
        ) as q
group by title, PerformanceStarts, IdPerformance, DoorsOpening, onsale, Active, showactive, VenueID

Answer 1

尝试将取消的条件移动到联接条件。

left join tickets t on t.PerformancePriceBandID = ppb.IdPerformancePriceBand and t.Cancelled = 0

并将其从place子句中删除。