How to remove duplicate values of two different tables with only two columns in common?

Question

0.00/5 (No votes)

See more:

hello , I have two tables with only two columns in common that I want to compare to each other and remove duplicate of the common columns

for example TABLE A has 4 columns and table B has 6 columns , I want to get the records of table A , BUT without the records where the rows matches ( when the values of the two common columns matches )

for example in the common two columns in tabe A there is code=x , Nature =y and in table B there is also code=x , Nature =y , so this record should be removed when I fetch the table A records ... I'm using visual studio c# and I didn't know what query to do to fetch these recrods ,any help please

What I have tried:

I tried many queries of union , minus ... but neither of them worked

Posted 3-Jan-21 15:09pm

Member 14190053

Updated 3-Jan-21 20:12pm

Add a Solution

2 solutions

Solution 1

The fact your two Tables are different in structure makes this potentially more complex.

In any case, you are going to need to learn how to use 'RowFilter, and the 'Select method of 'DataTable: [^]

// assume I have a DataTable 'dt with Integer Columns named 'X and 'Z

DataRow[] XZValuesSelected = dt.Select("X = 0 AND Z = 4");

I now have an Array of Rows where those specified values match ... however I cannot directly compare that to a similarly selected Row from another DataTable with a different structure !

Are the NAMES of the two columns with the same values the SAME in each Table ? The INDEXES in the column collections identical ?

"for example in the common two columns in table A there is code=x , Nature =y and in table B there is also code=x , Nature =y , so this record should be removed when I fetch the table A records"

By remove: I assume you mean FILTER table A, not actually remove a ROW ?

You want to Filter out ALL instances of rows where the two columns have identical values, or ONLY rows with identical values you specify ?

Can't read your mind :)

Posted 3-Jan-21 19:06pm

BillWoodruff

Comments

Member 14190053 5-Jan-21 11:48am

thank you for your reply and sorry for the bad introducing of my problem , the solution 2 was actually what i was looking for and solved the problem , ty for your time

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2021-01-03T20:12:00

Solution 2

Quote:
for example in the common two columns in tabe A there is code=x , Nature =y and in table B there is also code=x , Nature =y , so this record should be removed when I fetch the table A records (...) I didn't know what query to do to fetch these recrods ,any help please

If i understand you well, you're interesting in EXCEPT (Transact-SQL) statement[^]

SQL

SELECT a.code, a.Nature
FROM tableA a
EXCEPT
SELECT b.code, b.Nature
FROM tableB b

You can also use (NOT) EXISTS (Transact-SQL) statement[^] :

SQL

SELECT a.*
FROM tableA a 
WHERE NOT EXISTS
(
    SELECT b.*
    FROM tableB b
    WHERE b.code = a.code AND b.Nature = a.Nature
)

db<>Fiddle[^]

For further details, please read this:
Understanding the SQL EXCEPT statement with examples[^]
SQL Server EXISTS By Practical Examples[^]

Posted 3-Jan-21 20:12pm

Maciej Los

Updated 3-Jan-21 23:10pm

v3

Comments

BillWoodruff 4-Jan-21 3:18am

Hi Maciej, Does this work given the different column structure in the two DataTables ... given we don't know, yet, what those differences are.

Please, prove me wrong when I claim you cannot directly compare a selected Row from one DataTable with a Row from another DataTable that has a different Column structure ... without a much deeper kind of comparison :)

Maciej Los 4-Jan-21 3:43am

Hi, Bill!
First of all, i'm not sure what OP wants to achieve...
He mentioned that he wants to "remove duplicates", but it seems that he wants to select data from one table which are not in the second table based on comparable data in "common columns".

As you can see, i'm using pure sql commands.

As to your question...
If there's a set of columns with comparable data, you can compare them, even through the implicit/explicit conversion. It doesn't mean that a row (datarow) is equal to the row in another set.

Cheers!
Maciej

BillWoodruff 4-Jan-21 4:27am

"First of all, i'm not sure what OP wants to achieve..." Exactly why I went to the trouble of writing a lengthy reply reminding the OP what we need to know to further assist him.

"If there's a set of columns with comparable data" ... in this case we know the column structure in the tables is different.

Voted #1 for being a vague collage of assumptions and irrelevant code.

Maciej Los 4-Jan-21 4:51am

Well...
As to the "vague collage of assumptions and irrelevant code", please, read OP's question again, especially this part: I didn't know what query to do to fetch these recrods ,any help please. And further I tried many queries of union , minus ... but neither of them worked.

You're focused on datatable object (c#), i'm focused on table object (sql). At this point, my answer is complete. This is my point of view. Your does NOT have to be the same.

:(

Maciej Los 5-Jan-21 13:03pm

Bill, take a look at OP's comment. I was right. OP wanted to get data from table "A" which NOT exists in table "B" (based on 2 columns).
I hope you're not annoyed to me ;)
Cheers!

Member 14190053 5-Jan-21 11:54am

thank you for your reply and for the help ! you understood me , this is what I was looking for , both methods worked but what I needed was NOT EXISTS since I can get all the columns I want , thanks again

Maciej Los 5-Jan-21 12:50pm

You're very welcome!