remove record based on dup. field of two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

remove record based on dup. field of two tables

Author

Topic

mobardev
Starting Member

2 Posts

Posted - 2009-03-18 : 19:30:21

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.

Thanks

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.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-03-18 : 20:21:40

Not clear exactly what you're looking for, You don't wnat to remove any records from your table, just the result set.

Select a.*
from
tblMember a
left join
tblSurvey08 b
on
a.memberid = b. memberid
where
b.memberid is null

or
select a.*
tblMember a
where a.memberid not is (select memberid from tblSurvey08)

will give you all the members not surveyed in 08. The key here is the LEFT join.

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 11:20:11

or even

select a.*
tblMember a
where not exists (select 1 from tblSurvey08 where memberid =a.memberid)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources