Hey guys I'm debugging an issue where a specific record doesn't have a value and I'm trying to go about finding all records where this issue happens.
In my table there is a record for each Event for the same OrderId and ProductId. There should be a Event called ShipConfirm
and I want to find every ProductId or OrderId within my table that is missing that Event.
The table looks like this:
OrderId | ProductId | Event |
---|---|---|
1 | 9845 | Checkout |
1 | 9845 | CheckInventory |
1 | 9845 | SupplierAssignment |
Any help would be greatly appreciated!
You can use
Select distinct OrderId, ProductId from Table T
where Event != 'ShipConfirm'
and concat(OrderId,ProductId) not in (Select distinct concat(OrderId,ProductId)
from table where OrderId = t.OrderId
and ProductId = T.ProductId and Event = 'ShipConfirm');