7 Replies Latest reply: Sep 13, 2013 4:13 PM by rp0428 RSS

    outer join when there are Multiple tables are involved

    9876564

      Could not put up the question properly in the last chain, my problem is with the join when there are multiple tables are involved , here is just an example of the task which I need to achieve.

       

      Tab1 will have model id retailer_id information for all the weeks( starting from the first Monday) of Month JUNE with cost and rebat

      Tab1

      model_id

      retailer_id

      sell_date

      cost

      rebat

      1

      12

      3-Jun-13

      100

      40

      1

      12

      10-Jun-13

      200

      20

      1

      12

      17-Jun-13

      300

      20

      1

      12

      24-Jun-13

      400

      20

      2

      12

      3-Jun-13

      300

      10

      2

      12

      10-Jun-13

      200

      20

      2

      12

      17-Jun-13

      300

      20

      2

      12

      24-Jun-13

      400

      20

       

      Tab2:

      each retailer belongs to one dealer , below table has the information about the same

      retailer_id

      Dealer_id

      12

      100

      13

      100

      14

      101

      15

      101

      16

      101

       

       

      Tab3

      There is a third layer where each dealer is having a Dealer group

       

      Dealer_id

      Dealer_group

      100

      1001

      101

      1001

      102

      2001

      103

      2001

      104

      3001

      105

      3001

       

      Tab4:

      From the this table for each model and dealer group the discount information can be found   for the month of June(each week)

       

      model_id

      Dealer_group

      discount_date

      discount

      1

      1001

      3-Jun-13

      10

      1

      1001

      10-Jun-13

      20

      1

      1001

      17-Jun-13

      10

      1

      1001

      24-Jun-13

      30

      2

      1001

      3-Jun-13

      10

      2

      1001

      10-Jun-13

      20

      2

      1001

      17-Jun-13

      10

      2

      1001

      24-Jun-13

      30

      3

      2001

      3-Jun-13

      10

      3

      2001

      10-Jun-13

      20

      3

      2001

      17-Jun-13

      10

      3

      2001

      24-Jun-13

      30

       

      Master_info:

      This is the master table which is the Master table for Model /retailer information

      Model_id

      retailer_id

      1

      12

      2

      12

      3

      12

      4

      12

      1

      13

      2

      13

       

       

      Output

      model_id

      retailer_id

      sell_date

      cost

      rebat

      Final ( Cost-rebat-discount)

      1

      12

      3-Jun-13

      100

      40

      50

      1

      12

      10-Jun-13

      200

      20

      160

      1

      12

      17-Jun-13

      300

      20

      270

      1

      12

      24-Jun-13

      400

      20

      350

      2

      12

      3-Jun-13

      300

      10

      280

      2

      12

      10-Jun-13

      200

      20

      160

      2

      12

      17-Jun-13

      300

      20

      270

      2

      12

      24-Jun-13

      400

      20

      350

      3

      12

      3-Jun-13

      0

      0

      0

      3

      12

      10-Jun-13

      0

      0

      0

      3

      12

      17-Jun-13

      0

      0

      0

      3

      12

      24-Jun-13

      0

      0

      0

      4

      12

      3-Jun-13

      0

      0

      0

      4

      12

      10-Jun-13

      0

      0

      0

      4

      12

      17-Jun-13

      0

      0

      0

      4

      12

      24-Jun-13

      0

      0

      0

      1

      13

      3-Jun-13

      0

      0

      0

      1

      13

      10-Jun-13

      0

      0

      0

      1

      13

      17-Jun-13

      0

      0

      0

      1

      13

      24-Jun-13

      0

      0

      0

      2

      13

      3-Jun-13

      0

      0

      0

      2

      13

      10-Jun-13

      0

      0

      0

      2

      13

      17-Jun-13

      0

      0

      0

      1

      13

      24-Jun-13

      0

      0

      0

       

      For the above highted records( model_id / retailer_id combination) there are no records in tab1 but they do have entries in master_info so the records  should come for all those model_id/retailer_id  with all values as 0

        • 1. Re: outer join when there are Multiple tables are involved
          SomeoneElse

          It would have been so much better if you had posted your sample data in a format we could easily use.

           

          CREATE TABLE and INSERT statements would be OK.  Even WITH clauses would be fine.

           

          Something we can copy/paste right into a SQL Plus session.

          • 2. Re: outer join when there are Multiple tables are involved
            Frank Kulash

            Hi,

             

            It sure looks like a case for an outer join.  You might want start with a cross-join between master_info and a table (or result set) that has one row for each of the 4 weeks you want.  You may actually have a table like that, but if not, you can derive one, either from tab1 or tab45, or, depending on your requirements, from dual.  The other tables will be outer-joined to that.  I can show you exactly after you post the sample data, as Someoneelse mentioned.  Also, see the forum FAQ: https://forums.oracle.com/message/9362002

            • 3. Re: outer join when there are Multiple tables are involved
              9876564

              with tab1 as

              (

              select 1 model_id,12 retailer_id,to_date('3-jun-2013') sell_date,100 cost,40 rebat from dual

              union all

              select 1 model_id,12 retailer_id,to_date('10-jun-2013') sell_date,200 cost,20 rebat from dual

              union all

              select 1 model_id,12 retailer_id,to_date('17-jun-2013') sell_date,300 cost,20 rebat from dual

              union all

              select 1 model_id,12 retailer_id,to_date('24-jun-2013') sell_date,400 cost,20 rebat from dual

              union all

              select 2 model_id,12 retailer_id,to_date('3-jun-2013') sell_date,300 cost,10 rebat from dual

              union all

              select 2 model_id,12 retailer_id,to_date('10-jun-2013') sell_date,200 cost,20 rebat from dual

              union all

              select 2 model_id,12 retailer_id,to_date('17-jun-2013') sell_date,300 cost,20 rebat from dual

              union all

              select 2 model_id,12 retailer_id,to_date('24-jun-2013') sell_date,400 cost,20 rebat from dual

              )

              select * from tab1

               

               

              with tab2 as

              (

              select 12 retailer_id,100 dealer_id from dual

              union all

              select 13 retailer_id,100 dealer_id from dual

              union all

              select 14 retailer_id,101 dealer_id from dual

              union all

              select 15 retailer_id,101 dealer_id from dual

              union all

              select 16 retailer_id,101 dealer_id from dual

              ) select * from tab2

               

               

              with tab3 as

              (

              select 100 dealer_id,1001 dealer_group from dual

              union all

              select 101 dealer_id,1001 dealer_group from dual

              union all

              select 102 dealer_id,2001 dealer_group from dual

              union all

              select 103 dealer_id,2001 dealer_group from dual

              union all

              select 104 dealer_id,3001 dealer_group from dual

              union all

              select 105 dealer_id,3001 dealer_group from dual

              )

              select * from tab3

               

               

              with tab4 as

              (

              select 1 model_id,1001 dealer_group,to_date('3-jun-2013')discount_date,10 discount from dual

              union all

              select 1 model_id,1001 dealer_group,to_date('10-jun-2013')discount_date,20 discount from dual

              union all

              select 1 model_id,1001 dealer_group,to_date('17-jun-2013')discount_date,10 discount from dual

              union all

              select 1 model_id,1001 dealer_group,to_date('24-jun-2013')discount_date,30 discount from dual

              union all

              select 2 model_id,1001 dealer_group,to_date('3-jun-2013')discount_date,10 discount from dual

              union all

              select 2 model_id,1001 dealer_group,to_date('10-jun-2013')discount_date,20 discount from dual

              union all

              select 2 model_id,1001 dealer_group,to_date('17-jun-2013')discount_date,10 discount from dual

              union all

              select 2 model_id,1001 dealer_group,to_date('24-jun-2013')discount_date,30 discount from dual

              union all

              select 3 model_id,2001 dealer_group,to_date('3-jun-2013')discount_date,10 discount from dual

              union all

              select 3 model_id,2001 dealer_group,to_date('10-jun-2013')discount_date,20 discount from dual

              union all

              select 3 model_id,2001 dealer_group,to_date('17-jun-2013')discount_date,10 discount from dual

              union all

              select 3 model_id,2001 dealer_group,to_date('24-jun-2013')discount_date,30 discount from dual

              )

              select * from tab4

               

               

              with master_info as

              (

              select 1 model_id,12 retailer_id from dual

              union all

              select 2 model_id,12 retailer_id from dual

              union all

              select 3 model_id,12 retailer_id from dual

              union all

              select 4 model_id,12 retailer_id from dual

              union all

              select 1 model_id,13 retailer_id from dual

              union all

              select 2 model_id,13 retailer_id from dual

              )

              select * from master_info

              • 4. Re: outer join when there are Multiple tables are involved
                davidp 2

                It mostly sounds like a straight forward multiple outer join problem. It also sounds like homework, so I won't go too far. You should show you have worked on the problem.

                Work out the sequence of connections between the tables.

                It's easiest for multiple table outer joins if you do all the non-outer joins (connections where the relationships is always present) first followed by the outer joins (optional relationships). Once you have connected to a table by an outer join, connections from that table to other tables have to be outer joins too because the first table might not have been matched.

                E.g

                select ...

                from table1 join table2 on table1.widget_Id = table2.widget_Id

                join table3 on table3.emp_id = table1.emp_id

                join table3 on table4.fish_size = table3.fish_size and table4.color_id =table2.colour_id

                LEFT OUTER JOIN table5 on table5.fish_id = table4.fish_id

                LEFT OUTER JOIN table6.xxx = table5.xxx

                WHERE other tests

                Tests in the WHERE clause have to take allow for NULL values in outer joined tables - if there is no match you get a row with all NULLs for that table. E.g. "WHERE table5.weight < 10" will rejeect every row where the join to table5 didn't find a row, so you need "WHERE (table5.id IS NULL OR table5.weight < 10)"

                 

                Frank is right that getting the weeks might be difficult, unless every model will have data in table4 for every week, which is not true of your data.

                • 5. Re: outer join when there are Multiple tables are involved
                  Frank Kulash

                  Hi,

                   

                  Thanks for posting the sample data.

                  It's still unclear what dates you want to include in the output.  The following query shows how you can generate all Mondays in a given range.  If you want to include only the dates that are actually present in tabl1 and/or tab4, you can simplify this a little.

                   

                  WITH  date_range AS

                  (

                     SELECT  TRUNC ( TO_DATE ( '3-Jun-2013', 'DD-Mon-YYYY')

                                   , 'IW'

                                   )   AS first_monday

                     ,       TRUNC ( TO_DATE ('24-Jun-2013', 'DD-Mon-YYYY') + 6

                                   , 'IW'

                                   )   AS last_monday

                      FROM    dual

                  )

                  ,   all_mondays   AS

                  (

                      SELECT  first_monday + (7 * (LEVEL - 1)) AS sell_date

                      FROM    date_range

                      CONNECT BY  LEVEL <= 1 + ( (last_monday - first_monday)

                                               / 7

                                               )

                  )

                  SELECT    mi.model_id

                  ,         mi.retailer_id

                  ,         am.sell_date

                  ,         NVL (t1.cost,  0)           AS cost

                  ,         NVL (t1.rebat, 0)           AS rebate

                  ,         NVL (t1.cost,  0)

                            - ( NVL (t1.rebat,    0)

                              + NVL (t4.discount, 0)

                              )                         AS final

                  FROM             master_info  mi

                  CROSS JOIN       all_mondays  am

                  LEFT OUTER JOIN  tab1         t1  ON  t1.model_id      = mi.model_id

                                                    AND t1.retailer_id   = mi.retailer_id

                                                    AND t1.sell_date     = am.sell_date

                  LEFT OUTER JOIN  tab2         t2  ON  t2.retailer_id   = mi.retailer_id

                  LEFT OUTER JOIN  tab3         t3  ON  t3.dealer_id     = t2.dealer_id

                  LEFT OUTER JOIN  tab4         t4  ON  t4.model_id      = t1.model_id

                                                    AND t4.dealer_group  = t3.dealer_group

                                                    AND t4.discount_date = t1.sell_date

                  ORDER BY  mi.retailer_id

                  ,         mi.model_id

                  ,         am.sell_date

                  ;

                  The results aren't exactly what you said you wanted.  I suspect that's because of typos in what you posted.

                  • 6. Re: outer join when there are Multiple tables are involved
                    9876564

                    Though it was a straight forward problem for you, for me it became one of the best queries i have ever written ,  which is satisfying 100% of my functional  requirement.

                    Thanks A Lot Frank.

                    • 7. Re: outer join when there are Multiple tables are involved
                      rp0428
                      Though it was a straight forward problem for you, for me it became one of the best queries i have ever written ,  which is satisfying 100% of my functional  requirement.

                      What do you mean 'one of the best queries' that YOU have even written?

                       

                      You haven't posted ANY code at all.

                       

                      What query, exactly, did YOU write? Please post it so we can see why you think it is 'one of the best'.