This discussion is archived
6 Replies Latest reply: Nov 18, 2012 2:36 AM by S-Max RSS

An issue for analytical sql?

S-Max Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    @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 Newbie
    Currently Being Moderated
    @jeneesh,

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

Legend

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