I want to join two tables in talend based on <= join condition. How to achive this.
My output should be as below
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.
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....
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?
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.