2 Replies Latest reply: Jan 31, 2013 7:43 AM by shabar RSS

    Compare two table with Look up table

    shabar
      Hi All

      I basically I have 3 tables T1 T2 and T3 (Look up table)


      T1

      Col11     Col12     

      aa     888     
      aa     444     
      bb     666
      bb     447
      bb     478
      bb     999


      T2

      Col21     Col22     Col23

      123     hhh     lll
      123     hhh     ppp
      123     hhh     uuu
      345     ghg     kkj
      345     ghg     rer
      475     hhh     uyy


      T3

      Col31     Col32     

      aa     123
      aa     345
      aa     564
      bb     564
      bb     475


      I need to find out whether all the correspondance values(T3.Col31) of T1.Col11 are there in T2. If not what are the missing
      I have this T3 lookup table to match between T1 T3


      As a above data I need to get the following out put


      T1.Col11     T3.Col32     T2.Col22     T2.Col23

      aa          123          hhh          lll
      aa          123          hhh          ppp
      aa          123          hhh          uuu
      aa          345          ghg          kkj
      aa          345          ghg          rer
      aa          564          NULL          NULL


      What sort of query I can use to get the result


      Cheers


      Shabar