1 Reply Latest reply on May 20, 2014 8:36 AM by Steven Hawkins

    Question about query on 2 tables

    yang liu Newbie

      Hi all,

       

      I have a problem about the query on 2 tables, when I start a teiid and query like this:

       

      select crmDB.prospects_scottliu.id,crmDB.prospects_scottliu.phone_mobile,crmDB.prospects_scottliu.first_name,crmDB.prospects_scottliu.date_entered,teiid2602.KC.totalamount

      from crmDB.prospects_scottliu JOIN teiid2602.KC ON crmDB.prospects_scottliu.phone_mobile = teiid2602.KC.mobile

      where crmDB.prospects_scottliu.id is not null;

      ----------------------------

      the return result seems lose some record which should be retrieved, and the KC table has about 160k records, then I make a second try, I create a new table calld KD which copies the KC's schema, but I only insert 10 recoreds in it, then I query like this:

       

      select crmDB.prospects_scottliu.id,crmDB.prospects_scottliu.phone_mobile,crmDB.prospects_scottliu.first_name,crmDB.prospects_scottliu.date_entered,teiid2602.KD.totalamount

      from crmDB.prospects_scottliu JOIN teiid2602.KD ON crmDB.prospects_scottliu.phone_mobile = teiid2602.KD.mobile

      where crmDB.prospects_scottliu.id is not null;


      this time all results are retrieved.

      ----------------------------------

      and I have the third try, I add some some more conditions and try to retrieve the data which lose in the first attempt, the query like this:

      select crmDB.prospects_scottliu.id,crmDB.prospects_scottliu.phone_mobile,crmDB.prospects_scottliu.first_name,crmDB.prospects_scottliu.date_entered,teiid2602.KC.totalamount

      from crmDB.prospects_scottliu JOIN teiid2602.KC ON crmDB.prospects_scottliu.phone_mobile = teiid2602.KC.mobile

      where crmDB.prospects_scottliu.id is not null and crmDB.prospects_scottliu.phone_mobile in ('1111','2222','3333','4444') ;

      and this time all the records are retrieved, so I wonder if there is anything in teiid which limit the table when we try the query on 2 tables, if it is so, is anyone know how can I fix this problem, thanks.