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

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

There can be other Discount Type with other mandatory product see Order ID 4
There can be other Orders where Order Line Item Product ID = "abc" itself is not present. see Order ID 3

CodePudding user response：

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.

CodePudding user response：

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