2 Replies Latest reply: Apr 8, 2013 5:20 AM by Juan Gerardo Cabeza Luque RSS

    Outer Join Tables from MySQL Database on load.

      Hey everyone, i have been working all sunday through explanation on how to outer join tables. But i can not get it to work.

       

      Thats where i am coming from:

       

      table "product"

       

      product_idmanufacturer_id
      1a
      2b
      3c

       

      has first of all to merge with table: seo_product

       

      table "seo_product"

       

       

      product_idmanufacturer_id
      4a
      5b
      6c

       

      ( the product ids are unique in both tables so there is no ovelapp.

       

      After this two tables are joined i want them to join with the following

       

       

      has to join with:

       

      table "manufacturer"

       

       

      manufacturer_idmanufacturer_name
      abrand1
      bbrand2
      cbrand3

       

       

       

      table "category"

       

       

      product_idcategory_id
      1a
      2b
      3c

       

      and table "landingpage"

       

       

      product_idlandingpage_url
      1/x
      2/y
      3/z

       

      The whole data comes from a mysql database. I am sorry, to bother anyone, but i really couldnt get it to work.

       

      I need some starting point what to do after the

       

      ODBC CONNECT TO  database;

       

      ...

       

       

      Thanks in advance

        • Re: Outer Join Tables from MySQL Database on load.
          Kaushik Solanki

          Hi,

           

               Try below Code.

           

               Sql Select a.Product_ID,a.Manufacturer_ID,b.Manufacturer_Name,c.Category_ID,d.LandingPage_URL

               From Product a, Manufacturer b, Category c, landingpage d

               Where a.Manufacturer_ID =  b.Manufacturer_ID and

                          a.Product_ID = c.Product_ID and

                          a.Product_ID = d.Product_ID     

               Union All    

               Sql Select a.Product_ID,a.Manufacturer_ID,b.Manufacturer_Name,c.Category_ID,d.LandingPage_URL 

               From seo_product a, Manufacturer b, Category c, landingpage d

               Where a.Manufacturer_ID =  b.Manufacturer_ID and

                          a.Product_ID = c.Product_ID and

                          a.Product_ID = d.Product_ID

           

          Regards,

          Kaushik Solanki

          • Re: Outer Join Tables from MySQL Database on load.
            Juan Gerardo Cabeza Luque

            Hi chrisgerber,

                 First you have to concatenate the two first tables, not join them (Concatenate is like Union and Join is like Intersection). And then join the rest of tables, like this (I have attached an example with Excel data):

             

            LOAD product_id,

                 manufacturer_id

            FROM

            TestData.xlsx

            (ooxml, embedded labels, table is Product);

             

            Concatenate                    // --> This table has to be concatenated, not joined (Concatenate is like Union, Join like Intersection)

            LOAD product_id,

                 manufacturer_id

            FROM

            TestData.xlsx

            (ooxml, embedded labels, table is SEOProduct);

             

             

            Join

            LOAD manufacturer_id,

                 manufacturer_name

            FROM

            TestData.xlsx

            (ooxml, embedded labels, table is Manufacturer);

             

            Join

            LOAD product_id,

                 category_id

            FROM

            TestData.xlsx

            (ooxml, embedded labels, table is Category);

             

            Join

            LOAD product_id,

                 landingpage_url

            FROM

            TestData.xlsx

            (ooxml, embedded labels, table is LandingPage);