I have query that I can't get an outer join to work for.
It is supposed to retrun everyone in the jobs table with a address type of 'MA' or return the person with a blank if they do not have that address type. The problem is: They are in the table with other address types, so it does not know to return them. They exisit in the second table, so it is only returning the records that are equal between the 2. How do I get it to return the records from 1 whether they meet the criteria of the other or not?
from jobs a, addr a
where jobs_status = 'A'
and a.jobs_id(+) = a.addr_id
and a.addr_atyp_code = 'MA'
and jobs_effective_date = (select max(b.jobs_effective_date) from jobs b where b.jobs_pidm = a.jobs_pidm)
In other words, i want to see everybody from the jobs table whether they have a 'MA' mailing address or not, but they have other addresses in the table. I don't want to return those. The query above only returns where they have a mailing address.
Anyone have some insight?