asked on

How do I get the difference in 2 datatables

I have 2 datatables,

Table A Table B
1 3
2 4
3 5
6

I want the results to be

Table C
1
2

I thought this was called a left outer join but statement is not giving me the correct results

var results = (from rA in dtA.AsEnumerable()
join rB in dtB.AsEnumerable()
on rA.Field<string>(PrimaryKeyName) equals rB.Field<string>(PrimaryKeyName) into joinedRows
from ab in joinedRows.DefaultIfEmpty()
select ab);

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PadawanDBA

I mean... the SQL query would be:

select
   *
from
   tableA as A
      left join tableB as B on B.<col> = A.<col>
where
   B.<col> is null;

Open in new window

Jim Horn

Test code in SSMS..

IF EXISTS (SELECT name FROM sys.tables WHERE name='a') 
	DROP TABLE a

IF EXISTS (SELECT name FROM sys.tables WHERE name='b') 
	DROP TABLE b

CREATE TABLE a (val int) 
INSERT INTO a (val) VALUES (1), (2), (3) 

CREATE TABLE b (val int) 
INSERT INTO b (val) VALUES (3), (4), (5), (6)  

SELECT a.val
FROM a
   LEFT JOIN b ON a.val = b.val 
WHERE b.val IS NULL

-- or (SQL 2008 and beyond) 

SELECT a.val
FROM a
EXCEPT
SELECT b.val
FROM b

Open in new window

psdesignadmin

dtA.Merge(dtB ); //Here we marge the datatables
dtA.GetChanges();// Get Changes

Open in new window

Now we find the Distinct records of DataTable dtA

  dtC= dtA.DefaultView.ToTable(true, cols);

Open in new window

DefaultView returns the Distinct record but before that we have to pass the datatable as a column Array (cols) to this method.

~Tim

matthew phung

ASKER

I've requested that this question be deleted for the following reason:

My code was correct. My data was wrong.

Jim Horn

Cancelling the delete request, as multiple experts answered the question as it was asked.

If your question did not accurately reflect requirements, then please accept answer(s) here, finalize your requirements, and ask them in a new question.

psdesignadmin

Accept http:#a40500975 since it was the first to give a correct answer based on information given.

~Tim

PadawanDBA

^=== agree