topic Inner Join OR condition in Talend Studio

Inner Join OR condition

Rathesh — Sat, 16 Nov 2024 00:50:27 GMT

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

Re: Inner Join OR condition

Anonymous — Wed, 13 Jan 2021 03:50:39 GMT

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

Re: Inner Join OR condition

manodwhb — Wed, 13 Jan 2021 07:26:00 GMT

@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

Re: Inner Join OR condition

Anonymous — Wed, 13 Jan 2021 08:42:50 GMT

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

Re: Inner Join OR condition

Rathesh — Thu, 14 Jan 2021 18:57:22 GMT

@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

Re: Inner Join OR condition

Rathesh — Thu, 14 Jan 2021 19:01:07 GMT

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