2 Replies Latest reply: Feb 17, 2014 9:57 AM by Srikanth P RSS

    Joining 3 tables

      Dear Community,

       

      I've been busy with joining and concatenating, but I haven't found a solution yet.

      Hopefully you can help me out.

       

      1) connecting table "OrderVouchers"

       

      OrderID OrderVoucherID Amount

      1 1 10

      2 2 20

      3 3 20

      36 36 12,5

      36 37 12,5

      37 38 15

       

      2) Orders

      OrderIDtransactionIDCustomerIDCompanyIDOrderdateOrderTotal
      16102-2-201410
      29203-2-201420
      310304-2-201420
      36342012-2-201425
      373513012-2-201415

       

       

      3 ) Codes

       

      Codes
      OrderVoucherIDCodeIDCodedateValueCodeOnlineAdditional
      110 € 10,00x10
      220 € 20,00y10
      330 € 20,00z10
      36360 € 12,50b10
      37370 € 12,50c00
      38380 € 15,00d11

       

       

      The wanted output must be:

       

      OrderIDOrderVoucherIDCustomerIDOrderdateCodedateOrderTotalValueCodeOnlineAdditional
      1112-2-2014010 € 10,00x10
      2223-2-2014020 € 20,00y10
      3334-2-2014020 € 20,00z10
      3636212-2-2014025 € 12,50b10
      3637212-2-2014025 € 12,50c00
      37381312-2-2014015 € 15,00d11

       

      So 1 order can have multiple vouchers.

      It would be perfect to join the columns Orderdate and Codedate, because when one is filled with a date the other isn't, and visa versa.

       

      Hope my question is clear. If there are any questions, let me know.

       

      Kind regards,

       

      Erwin

        • Re: Joining 3 tables
          Byron Van Wyk

          As far as I can tell you can do the following to get the exact same output table as per above

           

          OrderVouchers:

          LOAD

               OrderID,

               OrderVoucherID

          From ....

           

          LEFT JOIN (OrderVouchers)

          Orders:

          LOAD

               OrderID,

               CustomerID,

               Orderdate,

               OrderTotal

          From ...

           

          LEFT JOIN  (OrderVouchers)

          Codes:

          LOAD

               OrderVoucherID,

               CodeDate,

               Value,

               Code,

               Online,

               Additional

          From ...

           

          • Re: Joining 3 tables
            Srikanth P

            Hi Erwin, Just join the tables on the right keys like below:

             

            OrderDetails:

            LOAD

                OrderID,

                OrderVoucherID

            From OrderVouchers ;

             

            LEFT JOIN (OrderVouchers)

            LOAD

                OrderID,

                CustomerID,

                Orderdate,

                OrderTotal

            From Orders;

             

            LEFT JOIN  (OrderDetails)

            LOAD

                OrderVoucherID,

                CodeDate,

                Value,

                Code,

                Online,

                Additional

            From Codes ;

             

            Please find the attached qvw for reference.