Remove all matching records. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Remove all matching records.

Author

Topic

scottdg
Starting Member

20 Posts

Posted - 2012-11-28 : 09:38:36

I have to run a query on people that paid for a donation to our foundation in 2011 but not in 2012. How do I run a query on 2 sets of data and then removes both matching records?

example:

2011 Donations 2012 Donations
ID Name Amount ID Name Amount
1 Bob Smith $15 4 Bob Smith $15
2 Al Smith $15 5 Jim Jones $15
3 Ted Smith $15 6 Steve Sanders $15

Result:
ID Name Amount
2 Al Smith $15
3 Ted Smith $15
2 Jim Jones $15
3 Steve Sanders $15

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-28 : 09:51:08

DECLARE @t2011 TABLE (ID int, [name] varchar(50),Amount money)
DECLARE @t2012 TABLE (ID int, [name] varchar(50),Amount money)

INSERT INTO @t2011
VALUES
(1,'Bob Smith','$15'),
(2 ,'Al Smith', '$15'),
(3,'Ted Smith','$15')

INSERT INTO @t2012
VALUES
(4,'Bob Smith','$15'),
(5,' Jim Jones','$15'),
(6, 'Steve Sanders', '$15')

select *
from @t2011 t11
where not exists(select * from @t2012 t12 where t11.name = t12.name)
union
select *
from @t2012 t12
where not exists(select * from @t2011 t11 where t11.name = t12.name)

Jim

Everyday I learn something that somebody else already knew

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-28 : 10:11:05

quote:
Originally posted by jimf

DECLARE @t2011 TABLE (ID int, [name] varchar(50),Amount money)
DECLARE @t2012 TABLE (ID int, [name] varchar(50),Amount money)

INSERT INTO @t2011
VALUES
(1,'Bob Smith','$15'),
(2 ,'Al Smith', '$15'),
(3,'Ted Smith','$15')

INSERT INTO @t2012
VALUES
(4,'Bob Smith','$15'),
(5,' Jim Jones','$15'),
(6, 'Steve Sanders', '$15')

select *
from @t2011 t11
where not exists(select * from @t2012 t12 where t11.name = t12.name)
union
select *
from @t2012 t12
where not exists(select * from @t2011 t11 where t11.name = t12.name)

Jim

Everyday I learn something that somebody else already knew

Not so often to see a reason for FULL OUTER JOIN, but here we can make use of it:

select
coalesce(t11.id, t12.id) as id,
coalesce(t11.name, t12.name) as name,
coalesce(t11.amount, t12.amount) as amount
from @t2011 as t11
full outer join @t2012 as t12 on t11.name = t12.name
where t11.name is null or t12.name is null

Too old to Rock'n'Roll too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-28 : 10:35:22

FULL OUTER JOIN? I think I need to look that one up!

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources