| Author |
 Topic  |
|
|
scottdg
Starting Member
18 Posts |
 Posted - 11/28/2012 : 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
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 11/28/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 11/28/2012 : 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
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 11/28/2012 : 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 |
|
|
| |
Topic |
|
|
|
Remove all matching records.
