Solved

T-SQL: Rookie Question on Best Approach for Selecting

Posted on 2011-09-20
7
192 Views
Last Modified: 2012-05-12
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        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.
0
Comment
Question by:anordquist
  • 5
7 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 36568584
could you do something like

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.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-%'  AND phone.phone_no IS NULL AND phone.phone_type IS NULL)

0
 

Author Comment

by:anordquist
ID: 36570057
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
0
 

Author Comment

by:anordquist
ID: 36570098
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.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36573150
Use Merge statement of SQL Server 2008 R2.

Merge Souce
Target
On

When Match
{;;;}
When not MAtche
{;;;}
0
 

Author Comment

by:anordquist
ID: 36573700
I'm running SQL Server 2005.
0
 

Accepted Solution

by:
anordquist earned 0 total points
ID: 36905683
Delete question, no solutions are forthcoming.
0
 

Author Closing Comment

by:anordquist
ID: 36935287
Delete the question, no answers for SQL Server 2005.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question