How can I compare two tables and delete on matching fields (not matching records)

Question

Scenario: A sampling survey needs to be performed on membership of 20,000 individuals. Survey sample size is 3500 of the total 20000 members. All membership individuals are in table tblMember. Same survey was performed the previous year and members whom were surveyed are in tblSurvey08. Membership data can change over the year (e.g. new email address, etc.) but the MemberID data stays the same.

How do I remove the MemberID/records contained tblSurvey08 from tblMember to create a new table of potential members to be surveyed (lets call it tblPotentialSurvey09). Again the record for a individual member may not match from the different tables but the MemberID field will remain constant.

I am fairly new at this stuff but I seem to be having a problem Googling a solution - I could use the EXCEPT function but the records for the individuals members are not necessarily the same from one table to next - just the MemberID may be the same.

Thanks

Answer 1

SELECT
   * (replace with column list)
FROM
   member m
LEFT JOIN
   tblSurvey08 s08
   ON m.member_id = s08.member_id
WHERE 
   s08.member_id IS NULL

will give you only members not in the 08 survey. This join is more efficient than a NOT IN construct. A new table is not such a great idea, since you are duplicating data. A view with the above query would be a better choice.

Answer 2

I apologize in advance if I didn't understand your question but I think this is what you're asking for. You can use the insert into statement.

insert into tblPotentialSurvey09
select your_criteria from tblMember where tblMember.MemberId not in (
    select MemberId from tblSurvey08
)

Answer 3

First of all, I wouldn't create a new table just for selecting potential members. Instead, I would create a new true/false (1/0) field telling if they are eligible.

However, if you'd still want to copy data to the new table, here's how you can do it:

INSERT INTO tblSurvey00 (MemberID) 
  SELECT MemberID
  FROM tblMember m
  WHERE NOT EXISTS (SELECT 1 FROM tblSurvey09 s WHERE s.MemberID = m.MemberID)

If you just want to create a new field as I suggested, a similar query would do the job.

Answer 4

An outer join should do:

select m_09.MemberID 
from tblMembers m_09 left outer join 
     tblSurvey08 m_08 on m_09.MemberID = m_08.MemberID 
where 
m_08.MemberID is null