Join data table activity - left Join not working as expected

Help Activities
, ,
1

I have two data tables Dt_S1 and Dt_S2, both have a common column. Using Join Data Tables Activity I tried to join the tables. But result is unexpected result.

I have given the tables below, the Result of the Join Data Table Activity is Dt_Result. In this Dt_Result table two new row were added and this is not expected.

Can someone help me out if I’m making any mistake in the activity, attached screenshot of join data Table activity Properties

Dt_S1
ProductID Price
123 10
456 11
123 12
Dt_S2
ProductID ProductName
123 pen
456 pencil
123 pen

When I used Left Join result is

Dt_Result
ProductID Price ProductID_1 ProductName
123 10 123 pen
123 12 123 pen
456 11 456 pencil
123 10 123 pen
123 12 123 pen

but Expected Result is

Expected Result
ProductID Price ProductID_1 ProductName
123 10 123 pen
456 11 456 pencil
123 12 123 pen

image

2

If you remove the third record from DT_S2 (123, pen) you will get your desired output :slight_smile:

3

we do see that 123 occurs twice. So the nature of join will create follwoing:

take first 123 from dt1 join it with

  • 123-pen from dt2
  • 123-pen from dt2

take the second 123 from dt1 join it with

  • 123-pen from dt2
  • 123-pen from dt2

thats why th 4 rows are found.

In such cases removing duplicates e.g in dt2 or custom join logic can help. here is just to rule when left 123 is picked up by which rule the both 123 rows from dt2 are to select for the pairing.

4

thank you for quick reply it worked

5

thank you for logical explanation