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!
CodePudding user response：
Aggregation provides one simple option:
SELECT OrderId, ProductId
FROM yourTable
GROUP BY OrderId, ProductId
HAVING SUM(Event = 'ShipConfirm') = 0;
CodePudding user response：
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');