T-SQL: Rookie Question on Best Approach for Selecting
Posted on 2011-09-20
I'm looking for the best approach on identifying, then deleting, some customer data.
There's tables for basic customer info, telephone/email info, and marketing info. Some customers have email info, some don't. The problem I'm trying to deal with is identifying those customers who don't an email address, yet have asked us to email them marketing info.
My select statement is:
SELECT entity.entity_id, phone.phone_no, phone.phone_type, attr_ent.attr_val
FROM (entity entity INNER JOIN attr_ent attr_ent ON (entity.entity_id=attr_ent.entity_id) AND (entity.co_cd=attr_ent.co_cd))
LEFT OUTER JOIN phone phone ON entity.phone_id=phone.phone_id
WHERE attr_ent.attr_val LIKE 'e-%'
It gets me data like:
00001003 NULL NULL E-Newsletter
00001003 NULL NULL E-Training Updates
00001035 952-123-4567 Direct E-Newsletter
00001035 email@example.com Email E-Newsletter
00001035 952-123-4567 Direct E-Training Updates
00001035 firstname.lastname@example.org Email E-Training Updates
00001240 507-321-9876 Fax E-Newsletter
00001240 507-321-9876 Fax E-Training Updates
Customer numbers 00001003 and 00001240 should have their requesst for marketing info deleted, as there's no where to send it. However, customer 00001035, has an email address and should get the marketing info.
I'm getting stuck doing the deletes on customers like 00001035 because I can't figure out how to identify them as valid requests.