Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2008
    Posts
    12

    Unanswered: Left Outer Join not working for multiple tables

    Hi All,

    I am having difficulties returning rows using LEFT OUTER JOIN on multiple tables. I am using Oracle 10G Express edition.

    The party table which is the main table has two rows but the address table has only one row. For some reason i am returning only one row for the first party and not the other. I have to make mu;tiple table joins. I have listed the joins below: Need Help!

    WHERE CDRCPTY_PIDM = SPRIDEN_PIDM(+)
    AND CDRCPTY_PIDM = SPRADDR_PIDM(+)
    AND CDRCPTY_PIDM = SPBPERS_PIDM(+)
    AND SPBPERS_ETHN_CODE = STVETHN_CODE(+)
    AND SPBPERS_HAIR_CODE = CTVHAIR_CODE(+)
    AND SPBPERS_EYES_CODE = CTVEYES_CODE(+)

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It looks like a WHERE clause which uses outer join between columns of unknown number of tables whose columns are also not known, as well as data stored in them.

    I believe Oracle wouldn't complain about the syntax, so - where's the problem?

    Oh, right! The problem is in the fact that we don't have enough information to provide the answer!

  3. #3
    Join Date
    Jan 2008
    Posts
    12
    Thanks for replying even if you did not have an answer. To clear the confusion my question is that how do we use LEFT OUTR JON for more than 3 tables ? In my online research i only found LEFT OUTER JOINS for 3 or less tables.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Show up the full query that you are having problems with. I have had left outer joins going to 20 tables, that is not a problem.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jan 2008
    Posts
    12
    I have pasted below the tables with the data and query.

    For one case:
    1) I have Party table CDRCPTY


    2) Then i have personal description table SPRIDEN





    3)Then i have address tables with only one row for that case


    I am expecting two rows, one for the RESP and other for PET party designation but i am only getting one row PET if i use the following query:

    SELECT
    CDRCPTY_PTYP_CODE AS party_designation
    ,CDRCPTY_PIDM AS unique_party_id
    ,SPRIDEN_FIRST_NAME AS first_name
    ,SPRIDEN_MI AS middle_name
    ,SPRIDEN_LAST_NAME AS last_name
    ,SPRADDR_STREET_LINE1||SPRADDR_STREET_LINE2||SPRAD DR_STREET_LINE3 AS address
    ,SPRADDR_CITY AS city
    ,SPRADDR_STAT_CODE AS state
    ,SPRADDR_ZIP AS zip
    ,SPRADDR_PHONE_AREA AS area_code
    ,SPRADDR_PHONE_NUMBER||SPRADDR_PHONE_EXT AS phone
    ,SPBPERS_SEX AS sex
    ,TRUNC(SPBPERS_HEIGHT / 12) AS height_ft
    ,MOD(SPBPERS_HEIGHT, 12) AS height_in
    ,SPBPERS_WEIGHT AS weight
    ,SPBPERS_ETHN_CODE AS race
    ,SPBPERS_HAIR_CODE AS hair
    ,SPBPERS_EYES_CODE AS eye
    ,TO_CHAR(SPBPERS_BIRTH_DATE, 'MM/DD/YYYY') AS dob




    FROM CDRCPTY,
    SPRIDEN,
    SPRADDR,
    SPBPERS




    WHERE CDRCPTY_PIDM = SPRIDEN_PIDM
    AND SPRIDEN_PIDM = SPRADDR_PIDM(+)
    AND SPRADDR_PIDM = SPBPERS_PIDM(+)



    AND CDRCPTY_CASE_ID = 'FFL092161'
    AND CDRCPTY_PTYP_CODE IN ('PET','RESP')
    AND CDRCPTY_END_DATE IS NULL
    AND SPRIDEN_CHANGE_IND IS NULL
    AND SPRADDR_FROM_DATE < SYSDATE
    AND (SPRADDR_TO_DATE >= SYSDATE
    OR SPRADDR_TO_DATE IS NULL)
    AND SPRADDR_STATUS_IND IS NULL

    AND NOT EXISTS (SELECT 'X'
    FROM CPRCONF
    WHERE CPRCONF_PIDM = SPRADDR_PIDM
    AND CPRCONF_ATYP_CODE = SPRADDR_ATYP_CODE
    AND CPRCONF_ADDR_SEQNO = SPRADDR_SEQNO);



    Then i changed my query to use LEFT JOIN syntax but it gives me an error that the SQL command not properly ended. I am not sure what synatx to use.

    SELECT
    CDRCPTY_PTYP_CODE AS party_designation
    ,CDRCPTY_PIDM AS unique_party_id
    ,SPRIDEN_FIRST_NAME AS first_name
    ,SPRIDEN_MI AS middle_name
    ,SPRIDEN_LAST_NAME AS last_name
    ,SPRADDR_STREET_LINE1||SPRADDR_STREET_LINE2||SPRAD DR_STREET_LINE3 AS address
    ,SPRADDR_CITY AS city
    ,SPRADDR_STAT_CODE AS state
    ,SPRADDR_ZIP AS zip
    ,SPRADDR_PHONE_AREA AS area_code
    ,SPRADDR_PHONE_NUMBER||SPRADDR_PHONE_EXT AS phone
    ,SPBPERS_SEX AS sex
    ,TRUNC(SPBPERS_HEIGHT / 12) AS height_ft
    ,MOD(SPBPERS_HEIGHT, 12) AS height_in
    ,SPBPERS_WEIGHT AS weight
    ,SPBPERS_ETHN_CODE AS race
    ,SPBPERS_HAIR_CODE AS hair
    ,SPBPERS_EYES_CODE AS eye
    ,TO_CHAR(SPBPERS_BIRTH_DATE, 'MM/DD/YYYY') AS dob




    FROM CDRCPTY
    WHERE
    CDRCPTY_CASE_ID = 'FFL092161'
    AND CDRCPTY_PTYP_CODE IN ('PET','RESP')
    AND CDRCPTY_END_DATE IS NULL

    LEFT OUTER JOIN
    SPRIDEN
    ON (CDRCPTY_PIDM = SPRIDEN_PIDM)
    AND SPRIDEN_CHANGE_IND IS NULL
    LEFT OUTER JOIN
    SPRADDR
    ON (SPRIDEN_PIDM = SPRADDR_PIDM)
    AND SPRADDR_FROM_DATE < SYSDATE
    AND SPRADDR_TO_DATE >= SYSDATE
    OR SPRADDR_TO_DATE IS NULL
    AND SPRADDR_STATUS_IND IS NULL

    AND NOT EXISTS (SELECT 'X'
    FROM CPRCONF
    WHERE CPRCONF_PIDM = SPRADDR_PIDM
    AND CPRCONF_ATYP_CODE = SPRADDR_ATYP_CODE
    AND CPRCONF_ADDR_SEQNO = SPRADDR_SEQNO)
    LEFT OUTER JOIN
    SPBPERS
    ON (SPRADDR_PIDM = SPBPERS_PIDM);

    Your help will be appreciated.

    Thanks
    Last edited by sadimalik; 05-06-08 at 18:39.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am expecting two rows
    Why do you expect two rows?

    >3)Then i have address tables with only one row for that case
    >1660214 18-Jul-06 2125 FOLSOM FAIR CIRCLE
    How does what appears to be 3 fields map to excerpt from query below
    Code:
    ,SPRADDR_STREET_LINE1||SPRADDR_STREET_LINE2||SPRAD DR_STREET_LINE3 AS address 
    ,SPRADDR_CITY AS city 
    ,SPRADDR_STAT_CODE AS state 
    ,SPRADDR_ZIP AS zip 
    ,SPRADDR_PHONE_AREA AS area_code 
    ,SPRADDR_PHONE_NUMBER||SPRADDR_PHONE_EXT AS phone
    too bad for all concerned that you have not clearly shown what you have
    & clearly explained the logic to achieve the results you desire
    Last edited by anacedent; 05-05-08 at 23:16.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Code:
    WHERE CDRCPTY_PIDM = SPRIDEN_PIDM
      AND SPRIDEN_PIDM = SPRADDR_PIDM(+)
      AND SPRADDR_PIDM = SPBPERS_PIDM(+)
      <some conditions on CDRCPTY>
      AND SPRIDEN_CHANGE_IND IS NULL
      AND SPRADDR_FROM_DATE < SYSDATE
      AND (SPRADDR_TO_DATE >= SYSDATE
        OR SPRADDR_TO_DATE IS NULL)
      AND SPRADDR_STATUS_IND IS NULL
    As you do not use outer join operator on all columns from left-joined tables (suppose the column prefix identifies it), so it produces inner join, as stated in http://download.oracle.com/docs/cd/B...6.htm#i2054062:
    If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.
    > Then i changed my query to use LEFT JOIN syntax but it gives me an error that the SQL command not properly ended. I am not sure what synatx to use.

    This is exactly the reason, why documentation was created. The Oracle one can be found eg. online on http://tahiti.oracle.com/.
    Also you may follow the previous link; for your information, it is for 10gR2 version.

  8. #8
    Join Date
    Jan 2008
    Posts
    12
    I have got the query working. I thought i should share it with you.It appears that i need to seperate conditions as below. The main table conditions will be placed in the WHERE Clause and the joined tables conditions will be placed after ON Clause. I also noticed that if i put the join condition (CDRCPTY_PIDM = SPRIDEN_PIDM) after ON clause the query does not work, so if i put another filtering criteria of the table and then the join condition, the query works. I did not completely understood why but the query works

    FROM CDRCPTY

    LEFT OUTER JOIN
    SPRIDEN
    ON
    SPRIDEN_CHANGE_IND IS NULL
    AND (CDRCPTY_PIDM = SPRIDEN_PIDM)

    LEFT OUTER JOIN
    SPRADDR
    ON
    SPRADDR_FROM_DATE < SYSDATE
    AND SPRADDR_TO_DATE >= SYSDATE
    OR SPRADDR_TO_DATE IS NULL
    AND SPRADDR_STATUS_IND IS NULL

    AND NOT EXISTS (SELECT 'X'
    FROM CPRCONF
    WHERE CPRCONF_PIDM = SPRADDR_PIDM
    AND CPRCONF_ATYP_CODE = SPRADDR_ATYP_CODE
    AND CPRCONF_ADDR_SEQNO = SPRADDR_SEQNO)

    AND (CDRCPTY_PIDM = SPRADDR_PIDM)

    LEFT OUTER JOIN
    SPBPERS
    ON
    (CDRCPTY_PIDM = SPBPERS_PIDM)

    WHERE
    CDRCPTY_CASE_ID = %case_number%
    AND CDRCPTY_PTYP_CODE IN ('PET','RESP')
    AND CDRCPTY_END_DATE IS NULL;

  9. #9
    Join Date
    Jan 2008
    Posts
    12

    ~ Be patient toward all that is unsolved in your heart and try to love the questions themselves. Do not now seek the answers, which cannot be given you because you would not be able to live them. And the point is to live everything. Live the questions ~
    ~ Be patient toward all that is unsolved in your heart and try to love the questions themselves. Do not now seek the answers, which cannot be given you because you would not be able to live them. And the point is to live everything. Live the questions ~
    Last edited by sadimalik; 05-06-08 at 21:58.

Posting Permissions

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