6 Replies Latest reply: Jan 13, 2011 6:19 AM by Kevin Rogner RSS

    Concatenate three tables

      Hi,

      I want to concatenate three tables. First I concatenated two of my tables.

      ToDos:
      SQL SELECT
      "No_",
      Date,
      "Contact No_",
      Description
      FROM "To-do" WHERE Status = 2;

      CONCATENATE (ToDos) SQL SELECT
      Name,
      "Company No_",
      "Responsibility Center",
      "Characteristic A",
      "Characteristic B"
      FROM Contact
      INNER JOIN "To-do" ON (Contact."No_" = "To-do"."Contact No_")
      WHERE ("To-do".Status = 2);

      This seems to work, but when I (right) click on Preview in table structure overview I see none of the joined columns is filled. Any suggestion?

       

      How would I add a third table where a Description to "Characteristic A" of Contact is stored?



        • Concatenate three tables
          John Witherspoon

          Sounds like you're after joins instead of concatenation.

          ToDos:
          LOAD
          "No_" as "To Do"
          ,"Date"
          ,"Contact No_" as "Contact"
          ,"Description"
          ;
          SQL SELECT
          "No_",
          ,"Date"
          ,"Contact No_"
          , Description
          FROM "To-do"
          WHERE Status = 2
          ;

          INNER JOIN (ToDos)
          LOAD
          "No_" as "Contact"
          ,"Name"
          ,"Company No_" AS "Company"
          ,"Responsibility Center"
          ,"Characteristic A"
          ,"Characteristic B"
          ;
          SQL SELECT
          "No_"
          ,"Name"
          ,"Company No_"
          ,"Responsibility Center"
          ,"Characteristic A"
          ,"Characteristic B"
          FROM Contact
          ;
          INNER JOIN (ToDos)
          LOAD
          "Characteristic" as "Characteristic A"
          ,"Characteristic Description" as "Characteristic A Description"
          ;
          SQL SELECT
          "Characteristic"
          ,"Characteristic Description"
          FROM Characteristics
          ;

          Alternatively, you can do the joins and renaming in the SQL. I don't, because instead I dump fairly raw tables to QVDs, and I'm doing the joins on loads from QVDs in the actual application.