How to find unpaired rows in TSQL?

Question

Using 2008 R2.

My table should contain some rows that are paired - a Cancel transaction and an Add transaction, but we have some bad data where there's just a Cancel.

I am attempting to identify these and eliminate them. I tried the following code but SQL returned the error "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

How can I identify these unmatched rows?

select *
from dbo.mytable t
where typename = 'Cancel'
except
Select *
from dbo.mytable t1 
inner join dbo.mytable t2
    on t1.id = t2.id
where t1.typename = 'Cancel'
and t2.typename = 'Add'

Answer 1

You have to specify the id field in the select list:

SELECT id
FROM mytable
WHERE typename = 'Cancel'

EXCEPT

SELECT id
FROM mytable
WHERE typename = 'Add'

The above query selects the id values for which an unmatched 'Cancel' record exists.

Alternatively you can use a LEFT JOIN:

SELECT t1.id
FROM mytable t1
LEFT JOIN mytable t2 ON t1.id = t2.id AND t2.typename = 'Add'
WHERE t1.typename = 'Cancel' AND t2.id IS NULL