SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Remove all matching records.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scottdg
Starting Member

20 Posts

Posted - 11/28/2012 :  09:38:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/28/2012 :  09:51:08  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 11/28/2012 :  10:11:05  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/28/2012 :  10:35:22  Show Profile  Reply with Quote
FULL OUTER JOIN? I think I need to look that one up!

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000