Inner Join OR condition

[Rathesh]

I want to join two tables in tMap using 6 columns in ON, out of that 2 should be in OR.

EG: SELECT * FROM A

INNER JOIN B

ON ​B.C1 = A.C1

AND B.C2 = A.C2

AND B.C3 = A C3

​ AND B.C4 = A.C4

AND (B.C5 = A.C5

OR B.C6 = A.C6)

How to implement this in talend?. I tried to mention C5 and C6 column in expression filter while joining,but it doesn't work

[Anonymous]

Hi

It is impossible to implement this in one join on tMap, as a workaround, do the join two times,

one join:

SELECT * FROM A

INNER JOIN B

ON ​B.C1 = A.C1

AND B.C2 = A.C2

AND B.C3 = A C3

​ AND B.C4 = A.C4

AND B.C5 = A.C5

another join:

SELECT * FROM A

INNER JOIN B

ON ​B.C1 = A.C1

AND B.C2 = A.C2

AND B.C3 = A C3

​ AND B.C4 = A.C4

AND B.C6 = A.C6

merge the two results using tUnite, and then filter the duplicated rows using tFilterRow

Hope it helps！

Regards

Shong

[manodwhb]

@Rathesh Varadarajan​ , you do the join till B.C4 = A.C4 in tmap and then in the output section enable filter and write the B.C5 = A.C5

OR B.C6 = A.C6 as filter condition for that output section.

Thanks,

Manohar

[Anonymous]

Good way, I didn’t think of it for a while, I still prefer this way. @Manohar B​ 

[Rathesh]

@manodwhb, I have tried this already in Input component expression filter, because I am using Unique match. In this scenario, I am not getting any records, but the record should come. If I mentioned in Output(as you told) and if record matched in both condition I will get 2 records. Then I can't apply Unique match model

[Rathesh]

@Shicong Hong , is there any custom component to implement this or will talend implement this in future release?