0

I have a table named as aviationDispute which has 2 columns deliveryId type=aviationdelivery and invoiceId type=AviationB2BDocuments , now when we raise a dispute from post man it will be eith against a delivery or either against an invoice both the columns can never be filled in one dispute .

The main problem is : my flexi query is only working when both the columns are filled , if one is filled and one remains then it does not gives result .

My flexi query is below

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%')

I have tried various combos of is null and is not null with brackets but nothing is working

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
siddarth
  • 1
  • 1

2 Answers2

1

What you need is left join

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%'
Adiputera
  • 171
  • 1
  • 2
  • 13
0

hope following query would help:

 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 will never be null in the result, because of natural join

Devendra
  • 1,864
  • 6
  • 29
  • 49