February 7, 2008 at 9:43 am
I am getting stuck here, and am not sure how to finish this. Basically i just need to find everyone with a no active addresses. What i have so far pulls only their inactive addresses, but i need everyone with NO active addresses. Any help much much appreciated. Thanks.
select distinct p.first_name
,p.last_name
,ad.address_type
,ad.status
from people as p
inner join addressschedule as ad
on p.people_code_id=ad.people_org_code_id
inner join peopletype as pt
on pt.people_code_id=p.people_code_id
where p.deceased_flag='n'
and pt.people_type='alum'
and ad.address_type in ('ETS','EMPL','BUS','ARBO','BRTH','HOME','PAR','LOCL','SUM','DNM','EMER','OTHR','GP')
and ad.status='i'
February 7, 2008 at 10:01 am
Will this work? It appears to, but id like confirmation. Thanks!
select distinct p.first_name
,p.last_name
,adi.address_type
,adi.status
from people as p
left outer join addressschedule as adi
on p.people_code_id=adi.people_org_code_id
and adi.status='i'
inner join peopletype as pt
on pt.people_code_id=p.people_code_id
where p.deceased_flag='n'
and pt.people_type='alum'
and adi.address_type in ('ETS','EMPL','BUS','ARBO','BRTH','HOME','PAR','LOCL','SUM','DNM','EMER','OTHR','GP')
and p.people_code_id in (select people_code_id
from people as p
left outer join addressschedule as ada
on p.people_code_id=ada.people_org_code_id
and ada.status='a'
where ada.people_org_code_id is null)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply