加入灵活搜索不适用于isnull关键字

Question

我有一个名为aviationDispute的表，它有两列deliveryId type=aviationdelivery和invoiceId type=AviationB2B文档，现在当我们从邮递员那里提出争议时，它将是针对交货还是针对发票，这两列永远不能在一个争议中填写。

主要问题是：我的弹性查询只有在两列都被填充时才起作用，如果其中一列被填充，另一列保留，那么它不会给出结果。

我的弹性查询低于

select {p:pk} from {AviationDispute as p join AviationB2BDocuments as a on {p:invoiceId}={a:pk} join AviationDelivery as d on {p:deliveryId} = {d:pk}} where ({d:deliveryId} LIKE '%searchTerm%' or {a:invoiceNumber} LIKE '%searchTerm%') 

我已经尝试了各种组合。is null和is not null with brackets，但什么都不起作用

Answer 1

您需要的是加入

select {p:pk} from {
AviationDispute as p 
left join AviationB2BDocuments as a on {p:invoiceId}={a:pk} 
left join AviationDelivery as d on {p:deliveryId} = {d:pk}
} 
where 
{d:deliveryId} LIKE '%searchTerm%' or {a:invoiceNumber} LIKE '%searchTerm%'

Answer 2

希望以下查询能有所帮助：

select {p:pk} from {AviationDispute as p join AviationB2BDocuments as a on {p:invoiceId}={a:pk} join AviationDelivery as d on {p:deliveryId} = {d:pk}} where {d:deliveryId} LIKE '%searchTerm%' and {a:invoiceNumber} is null or {a:invoiceNumber} LIKE '%searchTerm%'

deliveryId在结果中永远不会为null，因为是自然加入