I have 2 excel files Excel1 and Excel 2. I have connected with those file as a database and store the data into 2 data tables (dtData1, dtData2)
The intension is that bot should take all the data from the left table and and only the matching data form the right table.
I have created another data table(dtResults) so that query can add the data into that table. I have written the query but only 1 column is coming from right side. but i need 3 columns from right table.
Here is the query I have use
(From d1 In dtData1.AsEnumerable
Group Join d2 In dtData2.AsEnumerable On d1(0) Equals d2(0) Into gj = Group
From g In gj.DefaultIfEmpty
Select ra = {d1(0), d1(1) ,If(isNothing(g), Nothing, g(1)) }
Select dtResults.Rows.Add(ra)).CopyToDataTable
Thanks Peter for your reply when I am applying that it is not working. I was trying with the below also but not working as if takes one input for false. where it is false it should give the remining columns
(From d1 In dtData1.AsEnumerable
Group Join d2 In dtData2.AsEnumerable On d1(0) Equals d2(0) Into gj = Group
From g In gj.DefaultIfEmpty
Select ra = {d1(0), d1(1) ,If(isNothing(g), Nothing,g(1).g(2)) }
Select dtResults.Rows.Add(ra)).CopyToDataTable
assumption dtresults is prepared and within the right column structure
Give a try on
(From d1 In dtData1.AsEnumerable
Group Join d2 In dtData2.AsEnumerable On d1(0) Equals d2(0) Into gj = Group
From g In gj.DefaultIfEmpty
Let chk = isNothing(g)
Let ra2 = If(chk, new Object(){nothing,nothing,nothing}, new Object(){nothing, g(1), g(2)})
Let ra1 = new Object(){d1(0), d1(1)}
Let ra = ra1.Concat(ra2).toArray
Select dtResults.Rows.Add(ra)).CopyToDataTable