Sample for 9132209 (Anti-Join)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

Declare @table table (ID1 int, ID2 int, Num INT, Type char(1))

INSERT INTO @table 
VALUES 
 (1,     1,     1,    'A'),
 (1,     1,     2,    'A'),
 (1,     2,     3,    'A'),
 (1,     2,     4,    'A'),
 (2,     1,     1,    'A'),
 (2,     2,     1,    'B'),
 (3,     1,     1,    'A'),
 (3,     2,     1,    'A')


SELECT DISTINCT t1.ID1, t1.ID2 
FROM
    @table  t1
    LEFT JOIN @table t2
    ON t1.ID1 = t2.ID1
        and t1.Type <> t2.Type
WHERE
    t1.Type = 'A'
    AND 
    t2.ID1 IS NULL

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached