CODEWITHSUNDEEP
sql-serversql-server-2005
Tommy Fisk
Tommy Fisk

Reputation: 339

filter rows from one table that appear in another sql server

I am trying to perform a filtering operation in SQL Server 2005.

Example. Let's say we have two tables, Current and Reject.

Current = 1, 2, 3, 4, 5, 1, 2
Reject = 2, 3, 4

Current \ Reject = 1, 2, 5

As you can see, the two tables share values. I essentially just want to subtract those shared values and place them into a new table.

This is what I tried, but it didn't work. (A is the column to match on).

select * from Current left join
              Reject on csrp.a = rp.a
         group by Current.a, Reject.a
         having count(Current.a) > count(Reject.a)

Notice below that the element 2 was filtered out, even though it should not have been.

enter image description here

Martin Smith's answer worked for this case!

EDIT

Ok, so let's complicate it a little bit more. Let's say I have the exact same situation as before, except this time I need to match on three columns instead of just one?

Current = (1, 2, 3), (2, 3, 4), (2, 3, 4), (4, 5, 6), (7, 8, 9), (1, 2, 3)
Reject = (2, 3, 4), (4, 5, 6), (7, 8, 9)

Current \ Reject = (1, 2, 3), (2, 3, 4)

Upvotes: 0

Views: 278

Answers (1)

Martin Smith
Martin Smith

Reputation: 453426

WITH [Current](a, b, c)
     AS (SELECT 1, 2, 3 UNION ALL 
         SELECT 2, 3, 4 UNION ALL 
         SELECT 2, 3, 4 UNION ALL 
         SELECT 4, 5, 6 UNION ALL 
         SELECT 7, 8, 9 UNION ALL 
         SELECT 1, 2, 3),
     Reject(a, b, c)
     AS (SELECT 2, 3, 4 UNION ALL 
         SELECT 4, 5, 6 UNION ALL 
         SELECT 7, 8, 9 ),
     T(RN, a, b, c)
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY a, b, c ORDER BY (SELECT 0)),
                a,
                b,
                c
         FROM   [Current]
         EXCEPT
         SELECT ROW_NUMBER() OVER (PARTITION BY  a, b, c ORDER BY (SELECT 0)),
                a,
                b,
                c
         FROM   Reject)
SELECT DISTINCT a,
                b,
                c
FROM   T

Upvotes: 1

Related Questions