We help IT Professionals succeed at work.

How do I get the difference in 2 datatables

146 Views
Last Modified: 2016-02-18
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);
Comment
Watch Question

SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
PadawanDBAOperational DBA

Commented:
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 HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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

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

Author

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

My code was correct. My data was wrong.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.
Accept http:#a40500975 since it was the first to give a correct answer based on information given.

~Tim
PadawanDBAOperational DBA

Commented:
^=== agree
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.