Search code examples
sqlgoogle-bigquery

Find All Records with the same Id in SQL Table that are missing a specific record


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!


Solution

  • 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');