create or replace view dorstp_member2010 (pers_id, mbr_id, year, feepaid, renewed)
  as select dorstp_member.personid, dorstp_member.memberid,
        dorstp_membership.year, dorstp_membership.feepaid,
        dorstp_membership.year = 2010 as renewed
from dorstp_member, dorstp_membership
where dorstp_member.memberid = dorstp_membership.memberid
  and dorstp_membership.year = 2010


select personid as ppid, lastname, firstname, 
       locationid, familyid, statusid, agecatid,
       gender, member,
       pers_id, mbr_id,
       year, feepaid, renewed
from dorstp_people
left join
dorstp_member2010
on personid = pers_id
where member > 0


select personid as ppid, lastname, firstname, 
       locationid, familyid, statusid, agecatid,
       gender, member,
       pers_id, mbr_id,
       year, feepaid, renewed
from dorstp_people p
left join
(select dorstp_member.personid as pers_id, dorstp_member.memberid as mbr_id,
        dorstp_membership.year as year, dorstp_membership.feepaid as feepaid,
        dorstp_membership.year = 2010 as renewed
from dorstp_member, dorstp_membership
where dorstp_member.memberid = dorstp_membership.memberid
  and dorstp_membership.year = 2010) as m
on p.personid = m.pers_id
where member > 0


select personid, lastname, firstname, locationid, familyid, statusid, agecatid, gender, member,
       familyname,
       pers_id, mbr_id,
       year, feepaid, renewed
from
(select personid, lastname, firstname, locationid, p.familyid, statusid, agecatid, gender, member, familyname
from dorstp_people p, dorstp_family f
where p.familyid = f.familyid ) as pf
left join
(select me.personid as pers_id, me.memberid as mbr_id,
        ms.year as year, ms.feepaid as feepaid,
        ms.year = 2010 as renewed
from dorstp_member me, dorstp_membership ms
where me.memberid = ms.memberid
  and ms.year = 2010) as m
on pf.personid = m.pers_id
where member > 0