Click here to Skip to main content
14,932,707 members
Please Sign up or sign in to vote.
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
Updated 3-Jan-21 20:12pm

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[^]
   
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!
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 :)
   
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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900