outer joins and criteria

EXAMPLE TABLES:
    Table NAME fields:

        NAME_PERSON_ID
        NAME_NAME

    Table ADDR fields:
        ADDR_PERSON_ID
        ADDR_TYPE
        ADDR_STREET_LINE_1

    Assume there are two ADDR_TYPEs (A and B)     Assume only one address of each type can exist

GOAL:
    I want to return all names. In addition, I want to return an address of type A, if it exists for the name, or else NULLs.

ATTEMPTED SOLUTION:
    SELECT NAME_NAME, ADDR_STREET_LINE_1

      FROM ADDR, NAME
     WHERE ADDR_PERSON_ID(+) = NAME_PERSON_ID
       AND

             ADDR_TYPE IS NULL
             OR
             ADDR_TYPE = 'A'


           )

PROBLEM:
    While the outer join includes those names with no address whatsoever, it still excludes names that have only addresses of type 'B'.

ATTEMPTED SOLUTION:
    SELECT NAME_NAME,

           DECODE( ADDR_TYPE,
                   'A', ADDR_STREET_LINE_1,
                   NULL
                 ) ADDR_STREET_LINE_1
      FROM ADDR, NAME
     WHERE ADDR_PERSON_ID(+) = NAME_PERSON_ID
       AND

             ADDR_TYPE IS NULL
             OR
             ADDR_TYPE = 'A'
             OR NOT EXISTS
               (
                   SELECT *
                     FROM ADDR B
                    WHERE B.ADDR_PERSON_ID = NAME_PERSON_ID
                      AND B.ADDR_TYPE = 'A'
               )


           )

PROBLEM:
    However, this strategy of mirroring the WHERE clause criteria in a SELECT DECODE statement is impractical for me as the real WHERE clause criteria I am dealing with is *exstensive*.

SUMMARY QUESTION:
    Is there any other way to achieve my goal?

TIA,
Phil R Lawrence
prlawrence_at_lehigh.edu Received on Thu Oct 14 1999 - 13:40:05 CDT