Need a query to fetch orders which are missing order item x when order item y is present

sanderz178 0
Viewed 24

Suppose we have the following tables

Order Header

Order ID Status
1 Open
2 Open
3 Complete
4 Complete

Order Line Items

Order Item ID Order ID Product ID Discount Type
1-1 1 abc p
1-2 1 def null
1-3 1 ppp null
2-1 2 abc p
2-2 2 def null
3-1 3 def null
4-1 4 abc d
4-2 4 ddd null

The original requirement was that, whenever a Order has Order Line Item Product ID = "abc" and Discount Type = "p" then it is mandatory to have Order Line Item Product ID = "ppp". see Order ID 1

Some how in production there are orders where this mandatory product is missing. I want to create a SQL query to fetch such orders. see Order ID 2

Please note

  1. There can be other Discount Type with other mandatory product see Order ID 4
  2. There can be other Orders where Order Line Item Product ID = "abc" itself is not present. see Order ID 3
oracle-sqldeveloper sql
2 Answers

Since we are only interested in the order #'s

  • write query to get those orders having 'ABC' with discount P.
  • then reduce the query using the where clause via a correlated query to only return those orders without a line on the order 'ppp'. using a "NOT EXISTS"

.

SELECT OLI.orderID
FROM orderLineItems OLI
WHERE ProductID = ('ABC') and DiscountType = 'P'
AND NOT EXISTS (SELECT 1 --basically says exclude orders having productID of ppp
                FROM orderLIneItems IOLI
                WHERE IOLI.OrderID = OLI.OrderID
                 and IOLI.ProductID = 'ppp')
GROUP BY OLI. OrderID  --eliminate duplicate order #'s incase multiple ABC line exist but no ppp on a given order.

Now if we needed data from both sides...we could use a left join to join when ABC exists on one set, and ppp on another set for the same order. when the outer join is NULL then we return the order... but since we don't need additional data; the not exists operates a bit faster given proper indexes.

xQbert0 
select distinct "Order ID"   
from   (
       select "Order Line Items".*
             ,count(case when "Product ID" = 'abc' and "Discount Type" = 'p'then 1 end) over(partition by "Order ID" order by "Order ID") as cnt_abc_p
             ,count(case when "Product ID" = 'ppp' then 1 end) over(partition by "Order ID" order by "Order ID")                          as cnt_ppp
       from   "Order Line Items"
       ) "Order Line Items"
where  cnt_abc_p = 1 
and    cnt_ppp   = 0
Order ID
2

Fiddle

DannySlor0
Related