topic Re: Issue in inner join in Talend Studio

Issue in inner join

Anonymous — Fri, 27 Oct 2017 05:30:59 GMT

hi all,

I have 2 tables named as table1- account and table2- category.

I am performing inner join operation in talend based on one column in table 1 inner join another column in

table 2.

If I am passing the same query in MySQL I am getting 411483 records ..but when I am performing the same query logic in talend I am getting 409321 records.. some 2162 records are missed when I am performing in talend.

Could anyone please explain me why the records are getting missed.

Example query is :

select * FROM
(`account` `d`
JOIN `category` `map` ON (((IFNULL(`d`.`category_only`, '') = IFNULL(`map`.`category `, '')))));

In the above query the category_only is the column in account table and category is the column in category table..

the both columns data type is same (string) but the column naming convention is different.

Re: Issue in inner join

Anonymous — Fri, 27 Oct 2017 07:17:16 GMT

Hi,

What's your output target in your job design? Have you already checked if these 2162 records are rejected by your output ?

Best regards

Sabrina

Re: Issue in inner join

Anonymous — Fri, 27 Oct 2017 07:35:45 GMT

hi,

thank you for giving reply,

I gave tmysqloutput component as a output and I am getting 409321 records...

and I also tried with tlogrow to check .. here also I am getting 409321 records..

could you please give me a solution regarding this..

thanks,

injarapu

Re: Issue in inner join

ashif2 — Fri, 27 Oct 2017 07:37:47 GMT

Hi @Injarapu_1995,

tJoin always support tunique join. If there are multiple matches between lookup and Main , only unique match will come out .

Note : Reject captures main records which not present in lookup

[For Difference between tMap and tjoin : https://community.talend.com/t5/Troubleshooting-Development/The-differences-between-the-tJoin-and-tMap-components/ta-p/21708]

Your case like below:

there are multiple records with null value for category_only column in account table and multiple records with null value for category column in category table, in mysql it this kind of data results in cross join.

1 Null category record in the account table matches with all null categories in the category table. This happens because of the null handling in your where clause.

Problem is not anything with talend components, it because of the data .

If you want the achieve same output numbers ,use tMap with inner join Model, & All Match Model

Re: Issue in inner join

Anonymous — Fri, 27 Oct 2017 07:49:06 GMT

hi ... I totally agree with this solution...

I already used the same inner join and match model as all matches. But still I am facing the same issue I am getting 409321 records..

You can find the attached screenshot:

please help me in this........

regards,

injarapu

Re: Issue in inner join

Anonymous — Fri, 27 Oct 2017 07:49:06 GMT

hi ... I totally agree with this solution...

I already used the same inner join and match model as all matches. But still I am facing the same issue I am getting 409321 records..

You can find the attached screenshot:

please help me in this........

regards,

injarapu

sample.PNG
sample.PNG

Re: Issue in inner join

Anonymous — Fri, 27 Oct 2017 07:49:06 GMT

hi ... I totally agree with this solution...

I already used the same inner join and match model as all matches. But still I am facing the same issue I am getting 409321 records..

You can find the attached screenshot:

please help me in this........

regards,

injarapu

sample.PNG

Re: Issue in inner join

Anonymous — Fri, 27 Oct 2017 07:52:50 GMT

sorry that I didn't mention .. I used tmap to join two tables and gave inner join condition and also match model as all matches ... still I am facing the issue ..

please give me the solution ..

thanks,

regards,

injarapu

Re: Issue in inner join

ashif2 — Fri, 27 Oct 2017 08:18:23 GMT

Remove the Main and Lookup link, and Do the join by expression like below

(row1.COUNTRY==null?"":row1.COUNTRY)
.equals(row2.COUNTRY==null?"":row2.COUNTRY)

Re: Issue in inner join

Anonymous — Fri, 27 Oct 2017 10:38:55 GMT

hi sir,

thank you for your reply ..

I applied the logic you sent in a screenshot but I am unable to solve this issue.

Still I am getting 409321 records and 2162 records are missing.

please help me in this

Capture.PNG