10 Replies Latest reply: Feb 4, 2013 10:56 PM by Senthilkumar S RSS

    Need Sql query

    Senthilkumar S
      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
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Need Sql query urgent
            rp0428
            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
              Senthilkumar S
              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
                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
                    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
                    Senthilkumar S
                    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
                      my query is giving the expected results
                      • 8. Re: Need Sql query
                        Frank Kulash
                        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
                          Senthilkumar S
                          Hi Frank,

                          Thanks for your info...
                          I have implemented through prix-fixe query
                          • 10. Re: Need Sql query
                            Senthilkumar S
                            thanks