Six Stars

Issue in inner join

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.

8 REPLIES
Moderator

Re: Issue in inner join

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

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: Issue in inner join

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

Seven Stars

Re: Issue in inner join

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-tM...

 

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

 

 

Six Stars

Re: Issue in inner join

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

 

Six Stars

Re: Issue in inner join

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

 

Six Stars

Re: Issue in inner join

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

Seven Stars

Re: Issue in inner join

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)

 

1212121.JPG

Six Stars

Re: Issue in inner join

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