[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


T-SQL: Rookie Question on Best Approach for Selecting

Posted on 2011-09-20
Medium Priority
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.
Question by:anordquist
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
LVL 18

Expert Comment

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)


Author Comment

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

Author Comment

ID: 36570098
The prior comment got away from me.....

Customer table

Phone table      (used for phone, fax, website, email, etc.)

Attribute-customer table

Attribute table

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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 21

Expert Comment

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

Merge Souce

When Match
When not MAtche

Author Comment

ID: 36573700
I'm running SQL Server 2005.

Accepted Solution

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

Author Closing Comment

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

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

649 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