How can join two datatable and find a row according to a condition like using SQL statement query?

Hi guys,
I have 2 datatables and both of them has a column is Code that can mapping each other.
I need to join 2 datatable to find all rows in datatable1 has code values are equal code values in datatable2.
Example in SQL, we can write as below
select a.code, a.name, b.code, b.name
from datatable1 as a
left join datatable2 as b on a.code = b.code
How can I transfer above statement into uipath?
Please, would you help me?
Thank you,

we can use JOIN DATATABLE activity with uipath same as we do in sql

Cheers @ngocvk

@ngocvk
with a LINQ statement have a look here

was done for List but even can be done for Datatable using column for the join condition.
like Isolate multiple rows with duplicate column values

Specific statement can we provide if needed

I used Join data table activity but result was not okay, it only ok if use inner join :|.
Additional, I also add filter condition like WHERE condition in SQL

Yah
We need to choose the type of join we need to perform on our datatable inside the join datatable wizard
Cheers @ngocvk

@ngocvk
Can you provide some Sampledata and expected result? Thanks

Yeap,
I have 2 data tables: dt1(no, item, code) and dt2(id, name, code)
I need to filter all rows of dt1 that is not belong to dt2 and condition compare via “code”.
Above question, I think I will filer all rows exist both of dt1 and dt2 then filter dt1 again. But some days, I think that solution is not good.
Because, if execute in SQL, statement only simple as:
SELECT a.*
FROM dt1 as a
WHERE a.code NOT IN (SELECT b.code FROM dt2 as b)

@ngocvk
In General IT should be solvable with following

  • Find the Common rows with a Join
  • Identify the Not Contained rows within dt1 with an except using the Common rows result from above

Currently im on the way give me some time and i can Setup some Sample Code for you

1 Like

woah, thank you so much @ppr

@ngocvk

lets assume your 2 datatables have the variable name dt1, dt2
grafik

first Assign:
(From t1 In dt1.AsEnumerable
Join t2 In dt2.AsEnumerable
On t1(“code”).ToString.Trim Equals t2(“code”).toString.trim
Select t1).CopyToDataTable

second Assign:
dt1.AsEnumerable.Except(dtCommon.AsEnumerable,DataRowComparer.Default).CopyToDataTable

Ensure DataSetExtensions are referenced:

Kindly note: CopyToDatatable will throw an error if returned no of rows is null. If there is a risk of this then incorporate some checks for this as well

I did the statements very fast. Please give me some feedback on how it is working at your end. Thanks

2 Likes

@ppr: woah, thanks so much :slight_smile:
I will notify my result when I complete :star_struck::heart_eyes:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.