Left excluding Join

Help
1

Hi, I’m looking for query that can produce the below output.

image
image826×287 4.99 KB

Thanks
Vani

3 Likes
2

yah we can use JOIN DATATABLE ACTIVITY

Cheers @VanithaRajakumar

1 Like
3

The join data tables activity only supports inner join, left outer join and, full outer join. This person is looking to keep ONLY items from table1 that are not in table2.

@VanithaRajakumar it would be easy enough to write a loop on table1 and compare to table2 and import rows to output table, but I am not sure how to write a linq query for it so I will wait for someone else. Let me know if you’re interested in a loop solution though (would be more than acceptable if there were 100k or less rows I’d imagine)

4

@Dave… you are right.!! Am looking to keep only items not in Table2. Unfortunately my datatable is more than 200K. So looping might not be a good solution I believe.

Thanks
V

5

yah that doesnt matter, we can pass the table2 in right side and table1 in left side and if left join is used its gonna work isnt it
Cheers @VanithaRajakumar @Dave

6

@Palaniyappan no because that solution would be the same as a right outer join. In that case the output would be:
emp ID
111
333

And @VanithaRajakumar stated that he wants the out put to be:
emp ID
222
444

@VanithaRajakumar - I would still give the looping solution a try. Perhaps give it a stress test of 300% more rows than usual and see what processing speed you get.

7

@VanithaRajakumar I just thought of another way to do it that may work and would be quite fast, although it would be done in a few steps instead of a single step

You could combine table1 and table2 into a single table. Then use a linq query to pull out only items where count = 1

8

@Palaniyappan Inner and Left joins in Join datatable did not work for my requirement :frowning:

9

@Dave… You mean to check in loop? Also to mention, the data count will be more than 200k as well. So looking for some query to fetch the rows at once

10

Got it! This is a linq query that simulates the following sql query

SELECT dt1.*
FROM dt1
LEFT JOIN dt2 ON dt2.EmpID = dt1.EmpID
WHERE dt2.name IS NULL

The linq query is:

Assign outputTable = (From t1 In dt1.AsEnumerable() Group Join t2 In dt2.AsEnumerable() On t1.Field(Of String)("EmpID") Equals t2.Field(Of String)("EmpID") Into tg = Group From tcheck In tg.DefaultIfEmpty() Where tcheck Is Nothing Select t1).CopyToDataTable()

Here is a .xaml showing it working :slight_smile:. Currently it is joining only on employee ID, alter the query with an AND statement if you want to join on more than one field.

LeftExcludeJoin.xaml (10.4 KB)

12 Likes
11

Thank you very much @Dave . You saved my day. Can you please explain the linq query? I don’t understand this part “Into tg = Group From tcheck In tg.DefaultIfEmpty() Where tcheck Is Nothing Select t1)”. How can I build the linq query myself? The SQL query looks simple where as linq is not :frowning:

12

The variable names I chose (t1, t2, tg, tcheck) can be changed to anything. They are just remnants from the various code sequences spliced together haha.

It is grouping the t1 and t2 so you can check if the rows coming from t2 are null/empty tg.DefaultIfEmpty() and assigns that to the variable tcheck. Then the select statement grabs all of the rows where tcheck is not null/empty

1 Like
13

Thanks a lot @Dave and @Palaniyappan :slight_smile:

14

One more question. How can I remove rows directly from Table1 and yet retain the formulas for Table1? @Palaniyappan @Dave

15

do you have any validation to be used before removing rows or just to remove
Cheers @VanithaRajakumar

17

@Palaniyappan @Dave… I will create this as a new topic. Please reply there. Thanks

18

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