This discussion is archived
10 Replies Latest reply: Aug 19, 2012 1:26 PM by 949483 RSS

data from multiple tables

949483 Newbie
Currently Being Moderated
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
    A Tael Journeyer
    Currently Being Moderated
    Post CREATE TABLE and INSERT INTO statements, also what you've come up with so far.

    --Andy                                                                                                                                                                                           
  • 2. Re: data from multiple tables
    sb92075 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 7. Re: data from multiple tables
    949483 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks all It worked.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points