SQL server: Compare rows

Question

Hi SQL gurus, I need a query which can do the following operation. I have a table with 2 columns

ID Values
 1  1
 1  2
 1  3
 1  4
 2  2
 2  5
 2  6

if you see for ID 1 I have 1,2,3 and 4 as values and for ID 2 I have 2, 5 and 6.

I want to write a query which return the following

1(-) 4(-) 5(+) 6(+)

mean 1 and 4 are deleted and 5 and 6 are added by comparing the two ids.

Is it possible? Please let me know

Thanks

Answer 1

Something like:

(
SELECT T.Value FROM dbo.Table T WHERE T.ID = 1
EXCEPT
SELECT T.Value FROM dbo.Table T WHERE T.ID = 2
)
UNION
(
SELECT T.Value FROM dbo.Table T WHERE T.ID = 2
EXCEPT
SELECT T.Value FROM dbo.Table T WHERE T.ID = 1
)

That will get you the list of values that are associated with 1 but not 2, and 2 but not 1. You could easily multiply the values from one of those subqueries by -1 to differentiate them, or run them as two separate queries.

Answer 2

This will give you 1 & 4:

select a.values
from my_table a
where not exists (
select * from my_table b where b.values = a.values and b.ID = 2)
and a.ID = 1

and this will give you 5 & 6:

select a.values
from my_table a
where not exists (
select * from my_table b where b.values = a.values and b.ID = 1)
and a.ID = 2