6 Replies Latest reply: Sep 29, 2010 5:17 PM by Karl Pover RSS

    Join of tables

    Swetha.D

      I am trying to combine the above tables.Whats the best way to do this?

      Brief background of the data in the tables:

      1.Some rows are present in the both tables which needs to be merged

      2.Rows present in table 1 will or will not be present in the table 2 and vice-versa which should be retained.

      3.If a row is present in por_order_extract which is there in the pp_ewallet_transaction ,a column in the pp_ewallet_transaction table indicates the presence of the row in the other table(column name:order_uuid)

      4.some of the columns like date,app_name,app_partner ,reference_id are present in both the tables.

       

        • Join of tables
          Karl Pover

          You can't see the image because it's too small. Please attach image in the second tab 'Options' when you reply.

          • Join of tables
            Karl Pover

            According to point 1, you'll need to do a join and according to point 2 you'll need to do an outer join. The key between the 2 tables will be order_uuid so that will be the only column that has the same name between the 2 tables and the other fields like date, app_name, app_partner, reference_id should have different names in one of the tables, so that they won't be considered keys.

            Regards.

              • Join of tables
                Swetha.D

                I want the names of the other columns also to be same as I want to link date ,app_partner to other tables.

                Yesterday I Join the two tables with OuterJoin without using the Qualifier,it produced the result what I want for sample set of records,but I am not sure whether I am doing it correctly or not.

                  • Join of tables
                    Swetha.D

                    Sorry the result is not what I wanted.

                    Can you please say way to join the tables with only order_uuid and the other column names should be present

                      • Join of tables
                        Karl Pover

                        Do rows that share the same order_uuid have the same value in date and app_partner? If not, they will not be joined and should be treated differently as separate data. If the values are the same then you can use them in the join.

                        I don't know what the goal of the data model is, but I think you might want to do something that I sometimes do for invoices and payments. First, I'll concatenate the 2 tables in a transactions table so they share the same date, customer o supplier filters, but then I'll also do a join between the 2 tables to match the invoice with its corresponding payment(s). All I need to do that is join the payment id or id's in the invoice document tables with the payment id or id's in the payment document table to create a column in the invoice table with the a sum of what has been paid until that date.

                        What kind of information do you want to get out of your data model?