anordquist
asked on
T-SQL: Rookie Question on Best Approach for Selecting
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.phon e_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 john.doe@acme.com Email E-Newsletter
00001035 952-123-4567 Direct E-Training Updates
00001035 john.doe@acme.com 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.
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
LEFT OUTER JOIN phone phone ON entity.phone_id=phone.phon
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 john.doe@acme.com Email E-Newsletter
00001035 952-123-4567 Direct E-Training Updates
00001035 john.doe@acme.com 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.
ASKER
The marketing info is in the attribute table. There's one row per customer per marketing info type. I can delete the row safely if the phone.phone_type is NULL, but what happens when the check comes up with a phone.phone_type equal to "Phone" rather than to "E-Mail". The attribute table row gets deleted, even if there might be a later record in the phone table for that customer that has the email info.
These aren't the exact names for the columns
customer table
entity_id
attribute_id
These aren't the exact names for the columns
customer table
entity_id
attribute_id
ASKER
The prior comment got away from me.....
Customer table
entity_id
attribute_id
phone_id
Phone table (used for phone, fax, website, email, etc.)
phone_id
phone_number
phone_type
Attribute-customer table
customer_id
attribute_id
Attribute table
attribute_id
attribute_value
attribute_type
So, if a customer doesn't have an email address, yet has requested marketing info, I want to delete the corresponding row in the attribute-customer table. I need to be able to check all of the rows in the phone table for the customer before I can determine if a deletion is warranted.
Customer table
entity_id
attribute_id
phone_id
Phone table (used for phone, fax, website, email, etc.)
phone_id
phone_number
phone_type
Attribute-customer table
customer_id
attribute_id
Attribute table
attribute_id
attribute_value
attribute_type
So, if a customer doesn't have an email address, yet has requested marketing info, I want to delete the corresponding row in the attribute-customer table. I need to be able to check all of the rows in the phone table for the customer before I can determine if a deletion is warranted.
Use Merge statement of SQL Server 2008 R2.
Merge Souce
Target
On
When Match
{;;;}
When not MAtche
{;;;}
Merge Souce
Target
On
When Match
{;;;}
When not MAtche
{;;;}
ASKER
I'm running SQL Server 2005.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Delete the question, no answers for SQL Server 2005.
DELETE entity WHERE entity.entity_id IN(
SELECT entity.entity_id
FROM (entity entity INNER JOIN attr_ent attr_ent ON (entity.entity_id=attr_ent
LEFT OUTER JOIN phone phone ON entity.phone_id=phone.phon
WHERE attr_ent.attr_val LIKE 'e-%' AND phone.phone_no IS NULL AND phone.phone_type IS NULL)