Query to find missing rows 1

[crystalnewbie]

A sale transaction typically gets recorded in the Transaction table with at least 3 rows of data. But I've come across transactions that is missing the Sale record, like orderid 1001. I need help with the query to find all orderid's that are missing a "Sale" trans type record.


TransID OrderID TransType Description Price
1 1000 Sale Item 1 $10
2 1000 Tax Tax $.85
3 1000 Payment Payment $10.85
4 1001 Tax Tax $1.70
5 1001 Payment Payment $21.70

 

[r937]

select OrderID   
  from Transaction
 group
    by OrderID
having sum(case when TransType='Sale'
                then 1 else 0 end) = 0

rudy

http://r937.com/

SQL Consulting

 

[crystalnewbie]

Thanks Rudy!

My problem turned out to be more complicated than what I posted but your solution got me started on the right path and was able to solve it.