Query and Join help please

[javoine]

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?

select jobs_id,a.addr_atyp_code,addr_street_line1
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?

thanks

[hensa22]

try this,

select jobs_id,a.addr_atyp_code,addr_street_line1 
from jobs a
left join addr b  on   a.jobs_id = b.addr_id 
where 
jobs_status = 'A' 
and (a.addr_atyp_code = 'MA'  or b.addr_id is null)
and jobs_effective_date = (select max(b.jobs_effective_date) from jobs b where b.jobs_pidm = a.jobs_pidm)

I assume field addr_id on table addr is foreign key references jobs_id on table jobs.

one more thing, alias 'a' duplicate in your first post.
maybe if you post table structure it would be great help

[javoine]

Thanks for the response. Actually addr id is not located in both tables, they are only joined by the original id. I got a hint and did solve it though by doing the following:


select jobs_id,a.addr_atyp_code,addr_street_line1
from jobs a, (select and a.addr_atyp_code = 'MA'
from addr) a
where jobs_status = 'A'
and a.jobs_id(+) = a.addr_id
and jobs_effective_date = (select max(b.jobs_effective_date) from jobs b where b.jobs_pidm = a.jobs_pidm)

Someone tipped me off to narrow the selction in the table creation as opposed to after it.

I do appreciate the response though!

Cheers!