6 Replies Latest reply: Nov 18, 2012 4:36 AM by S-Max RSS

    An issue for analytical sql?

    S-Max
      Hi all,

      I am on the OracleXE 11gR2 and have the following requirements for the output of the select statement:
      Tables:
      GARAGE
      ======
       ID GARAGE_NAME
      --- -----------
        1    GARAGE_1
        2    GARAGE_2
        
      
      PERSONS
      =======
       ID GARAGE_ID NAME
      --- --------- -----
        1         1 NAME1_1
        2         1 NAME1_2
        3         1 NAME1_3
        4         1 NAME1_4
        5         1 NAME1_5
        6         1 NAME1_6
        7         2 NAME2_1
        8         2 NAME2_2
        9         2 NAME2_3
       10         2 NAME2_4
      
      CARS 
      ==== 
       ID GARAGE_ID CAR
      --- --------- -----
        1         1 CAR1_1
        2         1 CAR1_2
        3         1 CAR1_3
        4         1 CAR1_4
        5         2 CAR2_1
        6         1 CAR2_2
        7         1 CAR2_3
        8         1 CAR2_4
        9         1 CAR2_5
       10         1 CAR2_6
      The required output is:
      GARAGE_ID GARAGE_NAME CAR    PERSON
      --------- ----------- ------ -------
              1    GARAGE_1 CAR1_1 NAME1_1
              1    GARAGE_1 CAR1_2 NAME1_2
              1    GARAGE_1 CAR1_3 NAME1_3
              1    GARAGE_1 CAR1_4 NAME1_4
              1    GARAGE_1        NAME1_5
              1    GARAGE_1        NAME1_6
              2    GARAGE_2 CAR2_1 NAME2_1
              2    GARAGE_2 CAR2_2 NAME2_2
              2    GARAGE_2 CAR2_3 NAME2_3
              2    GARAGE_2 CAR2_4 NAME2_4
              2    GARAGE_2 CAR2_5 
              2    GARAGE_2 CAR2_6 
      How can I achieve this output?
      Is it an issue for analytical SQL?
      Dear community, I need your help!

      Kind regards
        • 1. Re: An issue for analytical sql?
          jeneesh
          Looks like just an outer join..

          But how are you jining cars and persons -- based on name?

          Or Something like this?
          with c as
          (
            select c.garage_id,c.car,g.garage_name g_name,
                   row_number() over(partition by c.garage_id order by c.id) rn
            from cars c,garage g
            where c.garage_id = g.id
          ),
          p as
          (
            select p.garage_id,p.name p_name,g.garage_name g_name,
                   row_number() over(partition by p.garage_id order by p.id) rn
            from persons p,garage g
            where p.garage_id = g.id
          )
          select nvl(p.garage_id,c.garage_id) garage_id,
                 nvl(p.g_name,c.g_name) garage_name,
                 car,p_name person
          from c 
               full outer join p on
                (c.garage_id = p.garage_id and c.rn = p.rn);
          
          GARAGE_ID GARAGE_NAME CAR    PERSON
          --------- ----------- ------ -------
                  1 GARAGE_1    CAR1_1 NAME1_1 
                  1 GARAGE_1    CAR1_2 NAME1_2 
                  1 GARAGE_1    CAR1_3 NAME1_3 
                  1 GARAGE_1    CAR1_4 NAME1_4 
                  1 GARAGE_1           NAME1_5 
                  1 GARAGE_1           NAME1_6 
                  2 GARAGE_2    CAR2_1 NAME2_1 
                  2 GARAGE_2    CAR2_2 NAME2_2 
                  2 GARAGE_2    CAR2_3 NAME2_3 
                  2 GARAGE_2    CAR2_4 NAME2_4 
                  2 GARAGE_2    CAR2_5         
                  2 GARAGE_2    CAR2_6         
          
           12 rows selected 
          If this is not what you want, please explain the logic to reach at your output..
          Edited by: jeneesh on Nov 17, 2012 3:15 PM
          • 2. Re: An issue for analytical sql?
            ranit B
            Hi Jeneesh,

            Can you please explain me what & how you achieved this...?
            I couldn't understand the use of ROW_NUMBER().

            Also, do u think the data is wrong in CARS table?
            CARS 
            ==== 
             ID GARAGE_ID CAR
            --- --------- -----
              1         1 CAR1_1
              2         1 CAR1_2
              3         1 CAR1_3
              4         1 CAR1_4
              5         2 CAR2_1
              6         1 CAR2_2
              7         1 CAR2_3
              8         1 CAR2_4
              9         1 CAR2_5
             10         1 CAR2_6
            I guess, the GARAGE_ID should be '2' for ID in (6,7,8,9,10).
            • 3. Re: An issue for analytical sql?
              Frank Kulash
              Hi,
              ranit B wrote:
              Hi Jeneesh,

              Can you please explain me what & how you achieved this...?
              I couldn't understand the use of ROW_NUMBER().
              See {message:id=4452968}
              • 4. Re: An issue for analytical sql?
                jeneesh
                ranit B wrote:
                Hi Jeneesh,

                Can you please explain me what & how you achieved this...?
                I couldn't understand the use of ROW_NUMBER().
                It seems, the OP wants to join his two tables - CARS and PERSONS. But they dont have a direct relationship ( Even though they are related through GARAGE, that is a many to many relationship.

                In this case, If OP wants to have a one to one relationship between CARS and PERSONS grouped by GARAGE_ID, we need to generate a UNIQUE IDENETIFIER in both the tables to join them. That is what ROW_NUMBER does.

                In my query, in the WITH CLAUSE there are two tables defined (C and P). Both, with the use of ROW_NUMBER have a UNIQUE KEY => combination of GARAGE_ID and ROW_NUMBER. Now it is easy to join them.

                ROW_NUMBER is partitioned based on GARAGE_ID, because the output OP requires is based on GARAGE_ID also.
                ORDR BY ID in the ROW_NUMBER may not be required - but as per the expected output given it is required..
                Also, do u think the data is wrong in CARS table?
                Yea, It seems wrong.. I have corected it in my sample..
                • 5. Re: An issue for analytical sql?
                  ranit B
                  @Jeneesh - Thanks a Ton!!
                  I separated each part of your query & tested, then understood what u were tryin to achieve. Really nice.

                  @Frank - Now will look into what you suggested. It 'll surely be an eye-opener, as always.
                  Your SQL queries always have something interesting... Please help everybody by writing a book... :-)

                  Thanks guys.
                  • 6. Re: An issue for analytical sql?
                    S-Max
                    @jeneesh,

                    your solution is nice and realy GREAT!
                    Thank you very-very much!!!