Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Question Unanswered: Inner/Outer Join Problem

    To all sql gurus:
    I've been racking my brain for the last few
    days experementing on Oracle 9i with all kinds of joins
    and inner joins to get a certain set of results, but haven't
    had any luck. Can anybody out there help me
    modify my sql statement to get the required results
    given to following setup?
    Thank you!!!
    Tony

    Sql:
    select tab1.A,tab1.B,tab2.C,tab2.D
    from i tab1, ii tab2, iii tab3
    where tab3.A = tab1.A (+)
    and tab3.A = tab2.A
    order by A,C

    Actual Results:
    A | B | C | D
    ----------------------------
    1 |Module 1 | 1 |Station 1
    3 |Module 3 | 2 |Station 2

    Desired Results:
    A | B | C | D
    ----------------------------
    1 |Module 1 | 1 |Station 1
    2 |Null |Null|Null
    3 |Module 3 | 2 |Station 2


    [Definitions]
    TABLE 'i'
    ----------
    A NUMBER,
    B VARCHAR2

    Table 'ii'
    -----------
    C NUMBER,
    D VARCHAR2

    Table 'iii'
    -----------
    A NUMBER,
    C NUMBER

    [Data]
    Table 'i'
    A | B
    ------------
    1 | Module 1
    2 | Module 2
    3 | Module 3


    Table 'ii'
    C | D
    -------------
    1 | Station 1
    2 | Station 2
    3 | Station 3

    Table 'iii'
    A | C
    -------------
    1 | 1
    3 | 2

  2. #2
    Join Date
    Mar 2004
    Posts
    14
    select tab1.A,tab1.B ,tab2.C,tab2.D , tab3.A
    from i tab1, iii tab3 , ii tab2
    where tab3.A (+) = tab1.A
    and tab3.A = tab2.c (+)
    order by tab1.A,C

  3. #3
    Join Date
    Mar 2004
    Location
    Sydney
    Posts
    20
    Desired Results:
    A | B | C | D
    ----------------------------
    1 |Module 1 | 1 |Station 1
    2 |Null |Null|Null
    3 |Module 3 | 2 |Station 2
    From the data you have posted the desired result does not make sense. If A =2 then B should equal "Module 2". So I am going to make this assumption and then we can say.
    Code:
    SQL> select tab13.*, tab2.D
      2  from (select tab1.A,tab1.B, tab3.C
      3    from tab1, tab3
      4    where tab3.A (+) = tab1.A
      5    order by A) tab13, 
      6    tab2
      7  where tab13.C = tab2.C (+)
      8  order by tab13.a, tab13.c;
    
             A   B                                  C D
    ---------- -------------------- ---------- --------------------
               1 Module 1                        1 Station 1
               2 Module 2
               3 Module 3                        2 Station 2
    nb. I have named the tables tab1, tab2 and tab3 rather than i, ii, iii.

  4. #4
    Join Date
    Mar 2004
    Posts
    14
    It's seems to me that result
    1 ----
    3 ---
    is more correct , because it means that there is no combination module/station on 2. May be you need describe your goal more particular.

  5. #5
    Join Date
    Mar 2004
    Posts
    7

    Thumbs up SQL GURUS ABOUND!

    Scoee was correct in his assumption. I didn't include 'Module 2' in column 'B' for my desired results. I plugged it in and both Scoee AND Andrew were correct with their suggestions. THANK YOU DEARLY...you guys ROCK!

    Tony

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •