How to accomplish a left join containing AND and OR conditions with ADF Mapping Data Flows

Marc van der Wielen 46

I need to join two tables based on a condition containing AND and OR logic.
Mapping data flows left join type only accepts AND conditions, according to the documentation I should use the join type Custom (cross) join, when I select this join type I am able to enter the condition with AND and OR logic but the result of this join only contains the records from both tables which match the condition. I need to keep the records from the first table as well like a normal LEFT JOIN would.

Please advice on how to accomplish this.

MartinJaffer-MSFT 26,026 Reputation points

2022-07-11T16:11:12.21+00:00

Hello @Marc van der Wielen and welcome to Mircrosoft Q&A.

Could you please give an example of the logic you want to implement while I look into making a cross join act like a left join?
Marc van der Wielen 46 Reputation points

2022-07-18T09:34:32.39+00:00
@MartinJaffer-MSFT I noticed that I can't accomplish this with the "left join" join type and I will need to develop some custom logic to simulate the left join. I have now decided to use the following approach:

I add a new column to the stream which contains a hash value over all columns in the record

I split the stream in to two branches, one branch containing all the records and another branch for which I perform a custom join containing the complex join condition, result of this join only contains the rows which matches the join condition

Then I perform a "does not exists" transformation in the other branch (which contains all records) on the hash column to keep only the rows which are not in the resultset of the custom join

Both streams (the stream containing the rows matching the join codition as well as the stream with the rows which do not match the join condition) will be combined together with a union transformation.

This approach works for me.