how would I limit the results to only one address based on a preference

Some of the donor ID’s have multiple mailing addresses, and I just need to select only the first one for each donor, and if there is no mailing address tied to a donor ID, then I need to pull a billing address, and if no billing address, then shipping address. but only show one address

Select d.donor_id, d.firstname,d.lastname, da.address_type_cd,address,
(da.city || ',' || da.state_province_cd || ' ' ||da.zip_postal_cd) as city_state_zip
from donor d
inner join donor_address da
on d.donor_id = da.donor_id
where da.address_type_cd = 'MAILING';

Answer

You could do something like this. Note the outer join – to make sure donors that don’t have an address are still shown in the result set (with null address columns). If this is not desired, change the join to inner join.

DATA FOR TESTING

create table donor (donor_id primary key, firstname, lastname) as
  select 101, 'Mia' , 'Soleno' from dual union all
  select 102, 'Ali' , 'Omer'   from dual union all
  select 108, 'Chen', 'Li'     from dual union all
  select 105, 'Ed'  , 'Torre'  from dual
;

create table donor_address (donor_id, address_type_cd, 
             address, city, state_province_cd, zip_postal_cd) as
  select 101, 'BILLING' , '33 Donor Lane', 'Boston' , 'MA', '83882' from dual
  union all
  select 101, 'BILLING' , 'Other Rd A/B' , 'Lebanon', 'MO', '30238' from dual
  union all
  select 101, 'SHIPPING', '132 Main #44' , 'Boston' , 'MA', '83877' from dual
  union all
  select 102, 'MAILING' , '4332 1st St.' , 'Austin' , 'TX', '23408' from dual
  union all
  select 102, 'SHIPPING', 'Lake Villa'   , 'Austin' , 'TX', '23411' from dual
  union all
  select 108, 'BILLING' , 'First Street' , 'Fresno' , 'CA', '92002' from dual
  union all
  select 108, 'MAILING' , '8 Last Street', 'Fresno' , 'CA', '92002' from dual
;

QUERY AND RESULT

with prep as (
  select donor_address.*,
         row_number() over (partition by donor_id
            order by case address_type_cd when 'MAILING'  then 1
                                          when 'BILLING'  then 2
                                          when 'SHIPPING' then 3 end) as rn
  from   donor_address
)
select d.donor_id, d.firstname, d.lastname, p.address_type_cd,
       p.address, p.city, p.state_province_cd, p.zip_postal_cd
from   donor d left outer join prep p on d.donor_id = p.donor_id and p.rn = 1
order  by donor_id
;

DONOR_ID FIRSTNAME LASTNAME  ADDRESS_ ADDRESS       CITY    STATE_ ZIP_POSTAL_
-------- --------- --------- -------- ------------- ------- ------ -----------
     101 Mia       Soleno    BILLING  33 Donor Lane Boston  MA     83882      
     102 Ali       Omer      MAILING  4332 1st St.  Austin  TX     23408      
     105 Ed        Torre
     108 Chen      Li        MAILING  8 Last Street Fresno  CA     92002