This discussion is archived
10 Replies Latest reply: Feb 4, 2013 8:56 PM by SenthilkumarS RSS

Need Sql query

SenthilkumarS Newbie
Currently Being Moderated
I have a 3 tables(sample) as below
Here Id is primary key for table1 and foreign key to table2 an table3.
table1     
ID     Name
1     Ram
2     Kumar

table2     
ID     dept
1     d1
1     d1
1     d1
2     d2
2     d2

table 3     
Id     Address
1     A1
1     A1
2     A2





I need to get o/p as below

1     Ram          d1          A1
1     Ram          d1          A1
1     Ram          d1          
2     Kumar      d2          A2
2     Kumar      d2          


Thanks,

Senthil

Edited by: 925896 on Feb 4, 2013 5:26 PM
  • 1. Re: Need Sql query urgent
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Need Sql query urgent
    rp0428 Guru
    Currently Being Moderated
    There is no 'urgent' in the forums.

    If you have an emergency you should contact Oracle support or hire a consultant.
  • 3. Re: Need Sql query
    SenthilkumarS Newbie
    Currently Being Moderated
    I have a 3 tables(sample) as below
    Here Id is primary key for table1 and foreign key to table2 an table3.
    table1
    ID  Name
    1   Ram
    2   Kumar
    table2
    ID dept
    1   d1
    1   d1
    1   d1
    2   d2
    2   d2
    table 3
    Id Address
    1  A1
    1  A1
    2  A2
    I used below query
    select * from table1 inner join table2 on table1.id=table2.id inner join table3 table1.id =table3.id
    Am getting o/p as below
    Id Name     dept  Address
    1  Ram       d1   A1
    1  Ram       d1   A1
    1  Ram       d1   A1
    2  Kumar     d2   A2
    2  Kumar     d2   A2
    I need to get o/p as below
    Id Name     dept  Address
    1  Ram       d1   A1
    1  Ram       d1   A1
    1  Ram       d1
    2  Kumar     d2   A2
    2  Kumar     d2
    Thanks,

    Senthil

    Edited by: 925896 on Feb 4, 2013 5:17 PM
  • 4. Re: Need Sql query
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Senthil,
    925896 wrote:
    I have a 3 tables(sample) as below
    Here Id is primary key for table1 and foreign key to table2 an table3.
    table1
    ID  Name
    1   Ram
    2   Kumar
    Whenever you have a question, please post CREATE TABLE and INSERT statements for your sample data, as descibed in the forum FAQ {message:id=9360002}
    table2
    ID dept
    1   d1
    1   d1
    1   d1
    2   d2
    2   d2
    table 3
    Id Address
    1  A1
    1  A1
    2  A2
    I used below query
    select * from table1 inner join table2 on table1.id=table2.id inner join table3 table1.id =table3.id
    Am getting o/p as below
    Id Name     dept  Address
    1  Ram       d1   A1
    1  Ram       d1   A1
    1  Ram       d1   A1
    2  Kumar     d2   A2
    2  Kumar     d2   A2
    Really? From the sample data above, I would expect 6 rows for id=1; like this
    Id Name     dept  Address
    1  Ram       d1   A1
    1  Ram       d1   A1
    1  Ram       d1   A1
    1  Ram       d1   A1
    1  Ram       d1   A1
    1  Ram       d1   A1
    2  Kumar     d2   A2
    2  Kumar     d2   A2
    when you post the CREATE TABLE and INSERT statements, be very careful that they are correct.
    I need to get o/p as below
    Id Name     dept  Address
    1  Ram       d1   A1
    1  Ram       d1   A1
    1  Ram       d1
    2  Kumar     d2   A2
    2  Kumar     d2
    Explain how you get those results from the sample data.
    Depending on why you want those results, you may want a Prix Fixe Query as shown in this thread: {message:id=4452824}
  • 5. Re: Need Sql query
    NSK2KSN Journeyer
    Currently Being Moderated
      SELECT t2o.id,
             t1.name,
             t2o.dept,
             t3o.address
        FROM table1 t1,
             (SELECT t2.*, ROW_NUMBER () OVER (PARTITION BY id ORDER BY dept) rn
                FROM table2 t2) t2o,
             (SELECT table3_gen.*,
                     ROW_NUMBER () OVER (PARTITION BY id ORDER BY address) rn
                FROM (SELECT id, address
                        FROM table3 t3o
                      UNION ALL
                      SELECT id, NULL
                        FROM (SELECT t2.id,
                                     ROW_NUMBER ()
                                        OVER (PARTITION BY id ORDER BY dept)
                                        rn
                                FROM table2 t2
                              MINUS
                              SELECT t3.id,
                                     ROW_NUMBER ()
                                        OVER (PARTITION BY id ORDER BY address)
                                        rn
                                FROM table3 t3)) table3_gen) t3o
       WHERE t2o.id = t3o.id AND t2o.rn = t3o.rn AND t1.id = t2o.id
    ORDER BY id,
             name,
             dept,
             address NULLS LAST
  • 6. Re: Need Sql query
    SenthilkumarS Newbie
    Currently Being Moderated
    Create table table1 (ID number primary key, name varchar2(10));
    insert into table1(1,'Ram');
    insert into table1(2,'Kumar');
    
    
    Create table table2 (ID number, dept varchar2(10));
    insert into table1(1,'d1');
    insert into table1(1,'d1');
    insert into table1(1,'d1');
    insert into table1(2,'d2');
    insert into table1(2,'d2');
    
    
    Create table table3 (ID number, address varchar2(10));
    insert into table1(1,'A1');
    insert into table1(1,'A2');
    insert into table1(2,'A3');
    Sorry I wrote wrong sample data previously.


    Its like as you said for Id=1
    Id Name     dept  Address
    1  Ram       d1   A1
    1  Ram       d1   A2
    1  Ram       d1   A1
    1  Ram       d1   A2
    1  Ram       d1   A1
    1  Ram       d1   A2
    But as per my requirement, whatever is there for Id in table3 that should come but not repetitively.

    Ex: for Id=1
    Id Name     dept  Address
    1  Ram       d1   A1
    1  Ram       d1   A2
    1  Ram       d1   
    1  Ram       d1   
    1  Ram       d1   
    1  Ram       d1   
    .
    .
    .
  • 7. Re: Need Sql query
    NSK2KSN Journeyer
    Currently Being Moderated
    my query is giving the expected results
  • 8. Re: Need Sql query
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    925896 wrote:
    Create table table1 (ID number primary key, name varchar2(10));
    insert into table1(1,'Ram');
    insert into table1(2,'Kumar');
    Thanks for posting the CREATE TABLE and INSERT statement. I suppose I needed to say that you have to post valid CREATE TABLE and INSERT statements. None of the INSERT statements you posted work; they are all missing the VALUES keyword.
    Create table table2 (ID number, dept varchar2(10));
    insert into table1(1,'d1');
    insert into table1(1,'d1');
    insert into table1(1,'d1');
    insert into table1(2,'d2');
    insert into table1(2,'d2');
    Should the rows above be INSERTed into table1, or table2?
    Create table table3 (ID number, address varchar2(10));
    insert into table1(1,'A1');
    insert into table1(1,'A2');
    insert into table1(2,'A3');
    Should the rows above be INSERTed into table1, or table3?
    Sorry I wrote wrong sample data previously.


    Its like as you said for Id=1
    Id Name     dept  Address
    1  Ram       d1   A1
    1  Ram       d1   A2
    1  Ram       d1   A1
    1  Ram       d1   A2
    1  Ram       d1   A1
    1  Ram       d1   A2
    But as per my requirement, whatever is there for Id in table3 that should come but not repetitively.
    I don't understand. The id from table3 does appear repetitively.
    Ex: for Id=1
    Id Name     dept  Address
    1  Ram       d1   A1
    1  Ram       d1   A2
    1  Ram       d1   
    1  Ram       d1   
    1  Ram       d1   
    1  Ram       d1   
    .
    .
    .
    Did you see the thread about the prix-fixe query?
    You can do something similar:
    WITH    numbered_table2          AS
    (
         SELECT     id, dept
         ,     ROW_NUMBER () OVER ( PARTITION BY  id
                                   ORDER BY          dept
                           )         AS r_num
         FROM    table2
    )
    ,       numbered_table3          AS
    (
         SELECT     id, address
         ,     ROW_NUMBER () OVER ( PARTITION BY  id
                                   ORDER BY          address
                           )         AS r_num
         FROM    table3
    )
    SELECT       t1.id
    ,       t1.name
    ,       t2.dept
    ,       t3.address
    FROM           numbered_table2  t2
    FULL OUTER JOIN      numbered_table3  t3  ON   t3.id    = t2.id
                                            AND  t3.r_num = t2.r_num
    FULL OUTER JOIN  table1         t1  ON   t1.id    = COALESCE (t2.id, t3.id)
    ORDER BY  t1.id
    ,            COALESCE (t2.r_num, t3.r_num)
    ;
    This query is similar to the one posted earlier, {message:id=10832389}. This query uses ANSI join notation, where the earlier query used the Oracle 8 syntax for doing a full outer join. The old style works in later versions of Oracle, too, but if you have Oracle 9 (or higher) you should use ANSI join syntax, especially for outer joins.
  • 9. Re: Need Sql query
    SenthilkumarS Newbie
    Currently Being Moderated
    Hi Frank,

    Thanks for your info...
    I have implemented through prix-fixe query
  • 10. Re: Need Sql query
    SenthilkumarS Newbie
    Currently Being Moderated
    thanks

Legend

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