OUTER JOIN problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: OUTER JOIN problem

  1. #1
    Join Date
    Mar 2001
    Posts
    26
    Hi All,

    I'd like to convert the following select statement from T-SQL to PL/SQL. When I run the following query in SQL Server, it works great. When I try to run it in Oracle (with Oracle syntax) I get the following error:

    "A table may be outer joined to only one other table"

    The following is the select statement:

    select a.attachmentid,v.serverid,s.AttachmentStatusID
    from attachment a,
    videoserver v,
    AttachmentServer s
    where a.videoid=v.videoid
    and a.videoid =1
    and s.attachmentid =* a.attachmentid
    and s.serverid =* v.serverid

    union all

    select a.attachmentid,v.serverid,s.AttachmentStatusID
    from attachment a,AttaccmentServer s,videoserver v
    where s.attachmentid = a.attachmentid
    and a.videoid=2
    and s.serverid *= v.serverid
    and a.videoid *= v.videoid

    union all

    select a.attachmentid,null,null
    from attachment a
    where a.videoid=2
    and a.attachmentid not in (select s.attachmentid from AttaccmentServer s)
    and a.videoid not in (select v.videoid from videoserver v)


    Any help would greatly be appreciated.

    - G

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    To avoid outer join, use UNION. Break your SELECT statement.

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Yoiks!

    Are you sure this is what you want to do? :)

    Okay, okay, assuming you know what you're doing, here is how to re-write it in Oracle:

    SELECT
    ___T.ATTACHMENTID_________,
    ___T.SERVERID____________,
    ___S.ATTACHMENTSTATUSID
    FROM
    ___(
    ______SELECT
    _________A.ATTACHMENTID______,
    _________V.SERVERID____________
    ______FROM
    _________ATTACHMENT_________A,
    _________VIDEOSERVER_________V
    ______WHERE
    _________A.VIDEOID _________=___1
    _________V.VIDEOID_________=___A.VIDEOID
    ___)__________________T___ ,
    ___ATTACHMENTSERVER___S
    WHERE
    ___AND___S.ATTACHMENTID (+)=___T.ATTACHMENTID
    ___AND___S.SERVERID______(+)=___T.SERVERID

    UNION ALL

    SELECT
    ___T.ATTACHMENTID_________,
    ___V.SERVERID____________,
    ___T.ATTACHMENTSTATUSID
    FROM
    ___(
    ______SELECT
    _________A.ATTACHMENTID_________,
    _________S.ATTACHMENTSTATUSID
    ______FROM
    _________ATTACHMENT_________A,
    _________ATTACHMENTSERVER___S
    ______WHERE
    _______________A.VIDEOID_________=___2
    _________AND___S.ATTACHMENTID______=___A.ATTACHMENTID
    ___)__________________T___,
    ___VIDEOSERVER_________V,
    WHERE
    ___AND___V.SERVERID______(+)=___T.SERVERID___
    ___AND___V.VIDEOID______(+)=___T.VIDEOID______

    UNION ALL

    SELECT
    ___A.ATTACHMENTID_________,
    ___V.SERVERID____________,
    ___S.ATTACHMENTSTATUSID
    FROM
    ___ATTACHMENT_________A,
    ___VIDEOSERVER_________V,
    ___ATTACHMENTSERVER___S
    WHERE
    _________A.VIDEOID_________=___2
    ___AND___S.ATTACHMENTID___(+)=___A.ATTACHMENTID
    ___AND___S.ATTACHMENTID___IS______NULL
    ___AND___V.VIDEOID______(+)=___A.VIDEOID
    ___AND___V.VIDEOID______IS______NULL


    There are a few things to note.

    - First of all, there is a fundamental difference between how SQLServer and Oracle treat outer-joins. In SQLServer, *all* regular joined predicates are performed first, followed by all outer-joined predicates. In Oracle, all outer-joined (bad term, BTW) non-table predicates are done, then outer-joins, then non-outer-join predicates on outer-joined tables. This sounds odd, but look at the last part of the UNION. Here, Oracle did the outer-join of the tables, *then* allows you to further restrict the set by those where the join produced a NULL. This is much faster than a NOT IN, and probably why they went with this method.
    - However, this method of processing invalidates the ability to outer-join to more than 1 table. This is only a minor inconvenicene, as it can be worked around with a sub-select in the FROM clause. *This* functionality did not used to exist in SQLServer, which is probably why they did *not* go with Oracle's methodology. Regardless, look at what I did with the first 2 parts and you'll understand. I turned the 2 tables into 1 - now we are no longer breaking the rule.

    One final note, the logic in your third select seems wrong. You are restricting to the set WHERE NOT IN and NOT IN. Therefore, if the record is in one, but not the other, it will still be in the result set. Therefore, if it is in S but not V, it will be included in the result set, *but* you hard-coded a NULL for the V.SERVERID field. You also hard-coded a NULL for the S.ATTACHMENTSTATUSID field, thereby not returning the actually value for records that pass your test. Another advantage of my solution is that I return the actual column. This should be included *whether or not* you want to include those records. If you don't want to include records that exist in *either* table, simply change the last part of the query to:

    ___AND___S.ATTACHMENTID___(+)=___A.ATTACHMENTID
    ___AND___V.VIDEOID______(+)=___A.VIDEOID
    ___AND___(
    ____________S.ATTACHMENTID___IS______NULL
    _________OR___V.VIDEOID______IS______NULL
    _________)


    Hope this helps. Let me know if you have any questions,

    - Chris

    BTW, replace _ with space or ___ with a 3-char tab. I only add these to keep formatting intact in this left-justifying forum :)

  4. #4
    Join Date
    Mar 2001
    Posts
    26
    Chrisrlong you were very helpful. I made some minor corrections to the query and everything worked.

    Thanks alot!

    - Gary

Posting Permissions

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



Click Here to Expand Forum to Full Width