10 Replies Latest reply: Aug 19, 2012 3:26 PM by 949483 RSS

    data from multiple tables

    949483
      I have 3 tables. Master,child1,child2.

      Master has one to many with child1.

      Master has one to many with child2.

      There is no relation between child1 and child2

      I want to retrive data from all 3 tables

      Table - Master
      id
      11
      12

      Table - child1
      id     |   deposited_date  |    Master_child1_reltion_key
      -----------------------------------------------------------------------------------------
      2      | 2012-10-10 | 11
      3      | 2012-10-10 | 11
      4      | 2012-10-10 | 11
      5      | 2011-01-01 | 12
      6      | 2005-02-20 | 13


      Table - child2
      id     | phoneNo              |  phone_type             | master_child2_relation_key
      -----------------------------------------------------------------------------------------------------
      15 | 1111111 | personal | 11
      16 |2222222 | office | 11
      17 |3333333 | home | 11
      18 |1010100 | personal | 12


      In table child2 there can be none,1,2, or 3 records against Master table.

      I wanted to retrive values from all 3 tables. like

      id      | deposited_date    | phoneno1    |  phoneno2    |  phoneno3
      --------------------------------------------------------------------------------------------------
      11 | 2012-10-10 | 1111111 | 2222222 | 3333333
      12 | 2011-01-01 | 1010100 | |
      13 | 2005-02-20 | | |

      Is it possible to retive using single query or need to use multiple queries ?
        • 1. Re: data from multiple tables
          Andy Tael-Oracle
          Post CREATE TABLE and INSERT INTO statements, also what you've come up with so far.

          --Andy                                                                                                                                                                                           
          • 2. Re: data from multiple tables
            sb92075
            946480 wrote:

            Is it possible to retive using single query or need to use multiple queries ?
            YES!
            • 3. Re: data from multiple tables
              O.Developer
              Sekect A.Id,
              B.col1, b.col2,
              c.col1,col2,col3


              from

              master a,
              child1 b,
              child2 c

              where

              a.id = b.id and

              a.id = c.id

              --------
              HOpe this will work ...Change col1 with proper column name...
              • 4. Re: data from multiple tables
                Frank Kulash
                Hi,


                946480 wrote:
                I have 3 tables. Master,child1,child2.

                Master has one to many with child1.
                As Andy said, you need to post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
                Explain, using specific examples, how you get those results from that data.
                Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
                You'll get better replies sooner if you always include this information whenever you have a question.

                See the forum FAQ {message:id=9360002}
                Master has one to many with child2.

                There is no relation between child1 and child2
                Really? It looks like child1.master_child1_relation_key = child2.master_child2_realtion_key.
                I want to retrive data from all 3 tables
                Why do you need the master table in this query? What do you want to get from master that's not already in the other tables?
                Table - Master
                id
                11
                12

                Table - child1
                id     |   deposited_date  |    Master_child1_reltion_key
                -----------------------------------------------------------------------------------------
                2      | 2012-10-10 | 11
                3      | 2012-10-10 | 11
                4      | 2012-10-10 | 11
                5      | 2011-01-01 | 12
                6      | 2005-02-20 | 13


                Table - child2
                id     | phoneNo              |  phone_type             | master_child2_relation_key
                -----------------------------------------------------------------------------------------------------
                15 | 1111111 | personal | 11
                16 |2222222 | office | 11
                17 |3333333 | home | 11
                18 |1010100 | personal | 12


                In table child2 there can be none,1,2, or 3 records against Master table.

                I wanted to retrive values from all 3 tables. like

                id      | deposited_date    | phoneno1    |  phoneno2    |  phoneno3
                --------------------------------------------------------------------------------------------------
                11 | 2012-10-10 | 1111111 | 2222222 | 3333333
                12 | 2011-01-01 | 1010100 | |
                13 | 2005-02-20 | | |
                What if there are 4 (or more) phone numbers for the same id?
                For id=11, there are 3 depostited_dates, but they happen to be the same. What results would you want if they were not all the same? Include an example or two when you post the CREATE TABLE and INSERT statements.
                Is it possible to retive using single query or need to use multiple queries ?
                I'm not sure what you want, but it looks like you can get it in a single query.
                If you really need a dynamioc number of output columns, then you made need two queries.
                • 5. Re: data from multiple tables
                  949483
                  Hello AppsLearner.Thanks for the reply.But I already tried this...but in my child2 table there will not be always records. Sometimes it will contain and sometimes it will not. But child1 there will be always records .So with and condition for child2 its failing.

                  Edited by: 946480 on Aug 18, 2012 5:57 AM
                  • 6. Re: data from multiple tables
                    sb92075
                    How do I ask a question on the forums?
                    SQL and PL/SQL FAQ
                    • 7. Re: data from multiple tables
                      949483
                      Here are my create table queries,

                      create table master(id number)

                      create table child1(id number,deposited_date Date,foreign key (*Master_child1*reltionkey) references master(id))

                      create table child2(id number,phone_no number,phone_type varchar2,foreign key (*Master_child2*reltionkey) references master(id))
                      • 8. Re: data from multiple tables
                        Venkadesh Raja
                        946480 wrote:
                        Here are my create table queries,
                         create table master(id number)
                        
                         create table child1(id number,deposited_date Date,foreign key (*Master_child1*_reltion_key) references master(id))
                         
                        create table child2(id number,phone_no number,phone_type varchar2,foreign key (*Master_child2*_reltion_key) references master(id))
                        Please post insert scripts and explain the logic clearly and also post the expected output.
                        • 9. Re: data from multiple tables
                          Etbin
                          Something to start with NOT TESTED! No Database at hand
                          with
                          master(rid) as
                          (select 11 from dual union all
                           select 12 from dual union all
                           select 13 from dual
                          ),
                          child_1(rid,deposited_date,master_id) as
                          (select 2,to_date('2012-10-10','yyyy-mm-dd'),11 from dual union all
                           select 3,to_date('2012-10-10','yyyy-mm-dd'),11 from dual union all
                           select 4,to_date('2012-10-10','yyyy-mm-dd'),11 from dual union all
                           select 5,to_date('2011-01-01','yyyy-mm-dd'),12 from dual union all
                           select 6,to_date('2005-02-20','yyyy-mm-dd'),13 from dual
                          ),
                          child_2(rid,phone_no,phone_type,master_id) as
                          (select 15,1111111,'personal',11 from dual union all
                           select 16,2222222,'office',11 from dual union all
                           select 17,3333333,'home',11 from dual union all
                           select 18,1010100,'personal',11 from dual
                          ),
                          select m.rid,
                                 c1.deposited_date,
                                 case c2.phone_type when 'personal' then c2.phone_no end p_phone,
                                 case c2.phone_type when 'office' then c2.phone_no end o_phone,
                                 case c2.phone_type when 'home' then c2.phone_no end h_phone
                            from master m,
                                 left outer join
                                 child_1 c1
                              on m.rid = c1.master_id
                                 left outer join
                                 child_2 c2
                              on m.rid = c2.master_id
                          Regards

                          Etbin
                          • 10. Re: data from multiple tables
                            949483
                            Thanks all It worked.