1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Multiple Outer Joins

Discussion in 'SQL PL/SQL' started by reylliam, Feb 1, 2011.

  1. reylliam

    reylliam Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Austin, TX
    Hello,

    I'm a relative novice with no formal Oracle training, (my background comes from VB) but generally get around SQL with no trouble until now... I have been asked to provide a report that I can't generate with my given knowledge set. I simply hadn't had this issue come up before. This is where you come in. :) I am using Toad for Oracle version 9.1.0.62, but this is more a basic SQL question than version dependent, I'm sure...

    I have a core pair of tables (let's say T1 and T2) that inner join together no problem; 1-to-1 match is a given. I have 5 "satellite" tables that each have records that pertain to *some* rows from T1/T2, as applicable to the needs of the application. The sets are independent, so some T1/T2 records will be pulling with no matching information on any satellite tables, some with information from all 5 satellite tables, and the rest from various combinations of the 5.

    The result set is supposed to present selected columns from all 7 tables, as a sort of master report. I expect NULL should return for columns coming from tables with no relevant records for T1/T2.

    I ran into the multiple outer join error trying to achieve this myself, so what, if any, workaround exists to bind these together into one query to return everything?

    Thanks kindly to any assistance in pointing me in the right direction.

    EDIT: I should add this error came about due to my use of (+) .. which is somewhat new to me and I may have misunderstood its application...

    [I did try to do a search for any document first, but I must not have searched correctly if one exists, so, apologies in advance if this was illustrated somewhere on the site already...]

    -Jason
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post your query so we can see what you are trying to do; also post the release of Oracle you're using as that can make a difference in how the query can be written.
     
  3. reylliam

    reylliam Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Austin, TX
    I figured that might become necessary, but I paraphrased the situation because I am somewhat stuck due to confidentiality agreements.. I hoped there was some general SQL construction rule to which I could be referred that could potentially assist with the situation. I will see what I can come up with. Thanks.
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi Jason,

    I tried reading your post twice, but it's very difficult to understand your requirement without having some sort of table structure and query which demonstrates what you are saying. I suggest you create your own tables with your own columns and replicate the issue and post your problem. Then you need not worry about company confidentiality rules and possibly can get a solution.
     
  5. reylliam

    reylliam Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Austin, TX
    Thank you for that suggestion! I will construct something and get back to you. FYI, I think I found my Oracle version - 9iR2 make sense?
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, from a marketing perspective. The banner displayed at login through SQL*Plus provides the actual release number, as does

    select banner from v$version;

    however for purposes your question 9iR2 will suffice. When you can construct a query demonstrating the error (and also post the error message) someone can hopefully assist you.
     
  7. reylliam

    reylliam Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Austin, TX
    this is my result for the sql you provided to obtain this -

    BANNER

    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE 10.2.0.4.0 Production
    TNS for HPUX: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production
     
  8. reylliam

    reylliam Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Austin, TX
    The environment from which I am writing is a hotel company central reservation system.

    The report generates all immediately necessary reservation information when the hotel's own reservation system is unavailable for any reason so that the hotel can still verify bookings.

    Here is the query using placebo column/table identifiers, and the last line is the remark advising the current error. Since I constructed this manually, I have an abbreviated version below showing 2 tables being bridged in instead of all 5... I figured whatever tactic applies to getting the 2 to work would just repeat for the additional tables 3 through 5.... but basically right now using the below, I have to resolve the error for the fact that I was bridging table a to table d as well as table a to table f using (+) because I want to be able to have all rows from a return regardless of whether matching rows exist on d or f, and then have relevant data from d return when applicable as well as relevant data from f return when applicable. Sometimes there will be no information from d or f to return, but I still want to see the rows from a.

    Make sense?

    Code (SQL):

    /* Formatted on 2011/02/02 10:31 (Formatter Plus v4.8.8) */
    SELECT   a.confirmation, a.property, a.arrival_date, a.departure_date,
             b.rateplan, b.rate_date, b.rate_amount, c.rm_seq, c.rm_type,
             c.rm_bed, c.nbr_adults, c.nbr_children, c.nbr_rollaways, d.rm_seq,
             d.remark_type, d.remarks, e.rm_seq, e.gst_seq, e.gst_last_name,
             e.gst_first_name, e.gst_mbrshp_lvl, e.gst_mbrshp_nbr, f.acct_type,
             f.acct_nbr, f.acct_name, f.acct_addr_line1, f.acct_addr_line2, f.acct_city,
             f.acct_state_prov_code, f.acct_postal_code, f.acct_country_code,
             a.booking_date, a.booking_userid
        FROM booking.res a,
             booking.res_rate b,
             booking.res_room c,
             booking.res_remarks d,
             booking.res_guest e,
             booking.res_accounts f
       WHERE a.current_rcrd_sts = 'Y'
         AND b.current_rcrd_sts = 'Y'
         AND c.current_rcrd_sts = 'Y'
         AND d.current_rcrd_sts = 'Y'
         AND e.current_rcrd_sts = 'Y'
         AND f.current_rcrd_sts = 'Y'
         AND a.confirmation = b.confirmation     -- every conf has at least 1 rate
         AND a.confirmation = c.confirmation     -- every conf has at least 1 room
         AND a.confirmation(+) =
                                d.confirmation
                                              -- not every conf has a remark on it
         AND a.confirmation =
                        e.confirmation
                                      -- every conf has at least 1 guest somewhere
         AND a.confirmation(+) =
                f.confirmation
                           -- not every conf has a travel agency or corporate acct
                           -- but some could have both
         AND c.rm_seq(+) =
                d.rm_seq
                   -- not every room has remarks; remarks are stored at room level
         AND c.rm_seq =
                    e.rm_seq
                            -- every room does have at least 1 guest in the 1 room
         AND a.cancel_ind = 'N'
         AND a.property = 12345
         AND a.booking_date BETWEEN TO_DATE ('01-01-2011', 'dd-mm-yyyy')
                                AND TO_DATE ('31-01-2011', 'dd-mm-yyyy')
    ORDER BY a.arrival_date, a.confirmation, e.gst_last_name

    /* ORA-01417: a table may be outer joined to at most one other table */
     
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The version you are using supports ANSI syntax for joins (from a inner join b on (b.x=a.x), from c full outer join d on (d.g=c.g), etc.) which could help greatly in allowing you to rewrite this query effectively. Using the antiquated version 6.0 syntax for outer joins is causing your problems; attempt a rewrite with the ANSI syntax [a full outer join does just that, outer joins two tables and returns all rows with nulls for missing values on both 'sides' of the join].
     
  10. reylliam

    reylliam Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Austin, TX
    Thanks kindly. It was no problem to do that, as I have written joins into the FROM statement in such a way from VB. I was being shown around in Oracle by someone who introduced me to (+), so I thought that was the NEW way I was supposed to do things - so imagine my surprise when you said antiquated! (I am easily misled :)) Anyway, I ended up with an explain plan of over 160 steps - my largest so far - and is taking quite awhile to complete, so now I am on my way through optimization at this point. Appreciate your assistance!
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It took Oracle a while to finally debug their ANSI syntax implementation but it can run rings around the old-style (+) syntax. It makes me wonder what other historical remnants this Oracle guide of yours has passed on as current knowledge.
     
  12. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I assume that was supposed to be 'not sure'. Even then, it's not clear what you are asking. The most common uncertainty on outer joins is "Where's the data?". Put another way -- which table does the join column value have to exist in? The (+) syntax made this easy to get confused about -- even for veteran coders, which is why I dropped it like a hot potato as soon as Oracle started supporting ANSI SQL. With ANSI SQL, there are three outer join possibilities: LEFT OUTER JOIN (often abbreviated to LEFT JOIN), RIGHT OUTER JOIN (likewise written commonly as RIGHT JOIN) and FULL OUTER JOIN. All three make it easy to determine where the data must exist.

    In the code block below is the same query joined in each of the three ways:

    In the first, the two tables will be joined by column JOINCOL. All rows where the value exists in both tables will be returned plus any rows in table T1 even though no JOINCOL match in T2 exists. The LEFT means that the table represented on the left side of the JOIN condition is the one where all rows will be returned.

    In the second, all rows where the value exists in both tables will be returned plus any rows in table T2 even though no JOINCOL match in T1 exists. The RIGHT means that the table represented on the rightt side of the JOIN condition is the one where all rows will be returned.

    In the third, all rows where the value exists in both tables will be returned plus any rows in table T2 even though no JOINCOL match in T1 exists plus any rows in T1 even though no JOINCOL match in T2 exists.

    Code (Text):
    SELECT t1.col1, t2.col2
    FROM   TABLE1 t1
           LEFT JOIN TABLE2 t2
           ON t1.joincol = t2.joincol;


    SELECT t1.col1, t2.col2
    FROM   TABLE1 t1
           RIGHT JOIN TABLE2 t2
           ON t1.joincol = t2.joincol;

    SELECT t1.col1, t2.col2
    FROM   TABLE1 t1
           FULL OUTER JOIN TABLE2 t2
           ON t1.joincol = t2.joincol;