multiple outer joins - how does that work ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: multiple outer joins - how does that work ?

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    multiple outer joins - how does that work ?

    I'm trying to create a table from 5 other tables using outer joins, but I get
    ORA-01417: a table may be outer joined to at most one other table

    How can I get round this ? Here's the basis for the table creation -

    SELECT COUNT(*) FROM
    PILOT.PERSON P
    ,PILOT.MEMBERSHIP M
    ,PILOT.HOUSEHOLD H
    ,PILOT.COMMUNICATION C
    ,PILOT.BASE G
    WHERE P.PRN(+) = M.PRN
    AND H.HRN(+) = M.HRN
    AND C.MEMBERSHIP_NUMBER(+) = M.MEMBERSHIP_NUMBER
    AND G.POSTCODE = H.POSTCODE(+)

    Any input would be greatly appreciated.

  2. #2
    Join Date
    May 2001
    Posts
    736
    Specify only one outer join (+) to this table, then retry the
    operation.

    "In a query that performs outer joins of more than two pairs of tables, a single table can be the NULL-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C."

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Use subqueries or use uninon fn...
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Sep 2001
    Posts
    37
    "In a query that performs outer joins of more than two pairs of tables, a single table can be the NULL-generated table for only one other table....
    In certain situations you have to use union and subquerys instead outer-joins because using them in the way that you need you break the rule described above in the quote..

    I don't know the logic of your table's structure , so maybe this query don't give the results that you expect. But you will note get an ORA-01417 error.

    SELECT COUNT(*) FROM
    PILOT.PERSON P
    ,PILOT.MEMBERSHIP M
    ,PILOT.HOUSEHOLD H
    ,PILOT.COMMUNICATION C
    ,PILOT.BASE G
    WHERE M.PRN(+) = P.PRN
    AND M.HRN(+) = H.HRN
    AND M.MEMBERSHIP_NUMBER(+) = C.MEMBERSHIP_NUMBER
    AND H.POSTCODE (+) = G.POSTCODE

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    ccastaneda :

    the suggested query will not give result wat he wanted....if number of records dont match in the tables M,P,H,C.....

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Sep 2001
    Posts
    37
    Originally posted by ccastaneda
    .. maybe this query don't give the results that you expect. But you will note get an ORA-01417 error.
    The point in my reply was to give him a query that don't get an ORA-1417 error. If the query don't give the results taht he expect the next step is use unions and subquerys.

    The only person who knows if this query give him the results that he expect is Horace. He knows his tables, the cardinalities of the relationships between the tables, the reason of the query itself, and all that stuff... deduce all that info from the joins of the query could not be accurated...

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