Compairing Data From 2 Tables

Table A

ID Product_code
1 aa
2 bb
3 cc
3 dd

Table B

ID Product_code
1 aa
2 bb
3 cc
3 dd
3 ee
4 aa

I need to compair both tables and return a list ID and Product_code
which is not in both tables ie

Result set

3 ee
4 aa

Thanks

Richard

Here it is,

Code Snippet

Create Table tablea (

[ID] int ,

[Product_code] Varchar(100)

);

Insert Into tablea Values('1','aa');

Insert Into tablea Values('2','bb');

Insert Into tablea Values('3','cc');

Insert Into tablea Values('3','dd');

Create Table tableb (

[ID] int ,

[Product_code] Varchar(100)

);

Insert Into tableb Values('1','aa');

Insert Into tableb Values('2','bb');

Insert Into tableb Values('3','cc');

Insert Into tableb Values('3','dd');

Insert Into tableb Values('3','ee');

Insert Into tableb Values('4','aa');

Code Snippet

select

isnull(a.id,b.id) id,

isnull(a.product_code, b.product_code) product_code

from

tablea a

full outer join tableb b

on a.id=b.id and a.product_code = b.product_code

where

a.id is nullor b.id is null

or

a.product_code is null or b.product_code is null

|||

SELECT * FROM TableA A
WHERE NOT EXISTS
(
Select * from TableB B WHERE A.Id = B.Id AND A.Product_code = B.ProductCode
)
UNION

SELECT * FROM TableB A
WHERE NOT EXISTS
(
Select * from TableB B WHERE A.Id = B.Id AND A.Product_code = B.ProductCode
)

otherwise you could also use INTERSECT / EXCEPT if you are using SQL Server 2005.

Jens K. Suessmeyer

http://www.sqlserver2005.de