problem with left outer join
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: problem with left outer join

  1. #1
    Join Date
    Jul 2005
    Posts
    23

    problem with left outer join

    I have a sybase query that must be converted to an oracle query but Im getting the error

    'a table may be outer joined to at most one other table'

    the query used to work in Sybase (i didnt originally write it, I just have to convert it) heres the SQL

    Select x, y, z (just for example purpose)
    FROM tmp_epg_txs_ids etd,
    txs_containers tc,
    aspect_ratios ar,
    formats f,
    ancsvc st,
    epg_templates et
    WHERE etd.id = tc.id
    AND tc.aspect_ratio_id (+)= ar.id
    AND tc.format_id (+)= f.id
    AND tc.ansv_method_st_code (+)= st.ancsvc_cod
    AND tc.epg_template_id (+)= et.id

    can anyone help ?

    TIA

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    For a left outer join in Oracle, the (+) goes on the right hand side of the "=" to indicate that this item might not be present. Assuming that "etd" and "tc" are present and the other rows may not be you should writ either of these statements in Oracle:

    Select x, y, z
    FROM tmp_epg_txs_ids etd,
    txs_containers tc,
    aspect_ratios ar,
    formats f,
    ancsvc st,
    epg_templates et
    WHERE etd.id = tc.id
    AND tc.aspect_ratio_id = ar.id (+)
    AND tc.format_id = f.id (+)
    AND tc.ansv_method_st_code = st.ancsvc_cod (+)
    AND tc.epg_template_id = et.id (+);

    Select x, y, z
    FROM tmp_epg_txs_ids etd
    JOIN txs_containers tc ON etd.id = tc.id
    LEFT OUTER JOIN aspect_ratios ar ON tc.aspect_ratio_id = ar.i
    LEFT OUTER JOIN formats f ON tc.format_id = f.id
    LEFT OUTER JOIN ancsvc st ON tc.ansv_method_st_code = st.ancsvc_cod
    LEFT OUTER JOIN epg_templates et ON tc.epg_template_id = et.id;

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Jul 2005
    Posts
    23

    left outer join

    thanks Tim

    Im a newbie to oracle, having come from SQL server where left outer join was *= and right outer join was =* (its the same in sybase also), its a bit confusing !

    Your solution worked, thank you.

  4. #4
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    At the risk of confusing misuk11 further, I prefer to write joins the other way round, in the way that my brain considers "forwards". My thinking here (and I appreciate other people seem to be wired up differently) is that you would not generally write
    Code:
    AND 'Smith' = e.ename
    and SQL syntax does not allow you to write
    Code:
    AND NULL IS e.deptno
    You instead put the the column to test on the left, and the value or expression you have already established on the right. Thus,
    Code:
    AND e.ename = 'Smith'
    AND e.deptno IS NOT NULL
    and likewise
    Code:
    SELECT x, y, z
    FROM   tmp_epg_txs_ids etd
         , txs_containers tc
         , aspect_ratios ar
         , formats f
         , ancsvc st
         , epg_templates et
    WHERE  tc.id = etd.id
    AND    ar.id (+) = tc.aspect_ratio_id
    AND    f.id (+) = tc.format_id
    AND    st.ancsvc_cod (+) = tc.ansv_method_st_code
    AND    et.id (+) = tc.epg_template_id;
    
    SELECT x, y, z
    FROM   tmp_epg_txs_ids etd
           JOIN txs_containers tc ON tc.id = etd.id
           LEFT OUTER JOIN aspect_ratios ar ON ar.i = tc.aspect_ratio_id
           LEFT OUTER JOIN formats f ON f.id = tc.format_id
           LEFT OUTER JOIN ancsvc st ON st.ancsvc_cod = tc.ansv_method_st_code
           LEFT OUTER JOIN epg_templates et ON et.id = tc.epg_template_id;
    Whichever way around you write it, all SQL cares about is that the (+) operator is immediately to the right of the key from the outer joined table.

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