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

Deivinho 0
Viewed 51

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!

google-bigquery sql
3 Answers

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');
Sandeep Dhakal0

Aggregation provides one simple option:

SELECT OrderId, ProductId
FROM yourTable
GROUP BY OrderId, ProductId
HAVING COUNT(CASE WHEN Event = 'ShipConfirm' THEN 1 END) = 0;
Tim Biegeleisen0

A slightly different answer:

with 
input as (
select 1 as OrderId, 9845 as ProductId, 'Checkout' as Event union all
select 1, 9845, 'CheckInventory' union all
select 1, 9845, 'SupplierAssignment'
)

select OrderId, ProductId
from input
group by OrderId, ProductId
having countif(Event = 'ShipConfirm') = 0
Xavier HAMIDA0
Related