Six Stars

How to perform non equi join with talend

Hi All, 

I want to join two tables in talend based on <= join condition. How to achive this.

 

Ex:

SRC1

ID NAME 

4 ABC

2 XYZ

SRC2

ID LOC

1 W

2 E

3 R

4 T

My output should be as below

TGT

ID NAME LOC

4 ABC W

4 ABC E

4 ABC R

4 ABC T

2 XYZ W

2 XYZ E

 

Please let me know if further details required.

 

Regards,

 

 

 

 

Veeranjaneyulu Boppudi
4 REPLIES
Twelve Stars

Re: How to perform non equi join with talend

An easy way to achieve this is to not join the Main to the Lookup in the tMap and just filter the output. This will essentially create a cartesian join and you filter the output table. Take a look at the image which shows how I have done this....

Screen Shot 2017-11-08 at 00.57.01.png

Rilhia Solutions
Twelve Stars

Re: How to perform non equi join with talend

Did this resolve your question? If so can you set it to resolved so that others can benefit from this?

Rilhia Solutions
Six Stars

Re: How to perform non equi join with talend

Hi What if i have thousands of records in other table and need to join only 3 or 4 records from main table. still need to do cross join? how about performance?

 

Regards,

 

Veeranjaneyulu Boppudi
Twelve Stars

Re: How to perform non equi join with talend

Performance of a cross join is always going to be poor. But you can mitigate for that by dynamically modifying the WHERE the clause of your lookup query with values from your main query (if, as you say, you only want a few values). This is very simple using Talend. You simply need to build a dynamic WHERE clause from the datarows returned from your MAIN query before it is passed to the tMap, then use that WHERE clause in your lookup. 

Rilhia Solutions