Find Missing Row using SQL

Question

I have a table TRANSACTIONS with a columns:

TRANSACTIONKEY (int) Primary Key
TRANSACTIONTYPEKEY (int) 
RECEIPTNUMBER (varchar)

TRANSACTIONTYPEKEY can have values that range from 1 to 50. There MUST be a TRANSACTIONS row with a TRANSACTIONTYPEKEY = 1 and then there can be any number of other TRANSACTIONTYPEKEY entries - even duplicates of some (another question for another time).

I have been asked to provide a report that shows all receipts (RECEIPTNUMBER) that have entry for TRANSACTIONTYPEKEY = 50 but no TRANSACTIONTYPEKEY entry = 1. Management has identified a half dozen by accident and wants to know the full extent and this table holds nearly 50,000 rows of transactions.

Is this possible with SQL? I am using SQL Server 2000 and T-SQL to be specific.

I have tried

SELECT RECEIPTNUMBER 
FROM   TRANSACTIONS 
WHERE  TRANSACTIONTYPEKEY = 50 
AND    TRANSACTIONTYPEKEY NOT IN (SELECT TRANSACTIONTYPEKEY FROM TRANSACTIONS WHERE TRANSACTIONTYPEKEY <> 1)

but this query goes on and on! I have to cancel it.

Answer 1

Try this:

SELECT a.RECEIPTNUMBER  
    FROM TRANSACTIONS  a
 WHERE a.TRANSACTIONTYPEKEY = 50  
   AND  NOT EXISTS
                (
                    SELECT 1 
                      FROM TRANSACTIONS b
                     WHERE b.RECEIPTNUMBER = a.RECEIPTNUMBER 
                       AND b.TRANSACTIONTYPEKEY = 1
                )