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.
| Author |
Topic |
|
dtrivedi
Posting Yak Master
153 Posts |
 Posted - 2010-12-02 : 13:46:45
|
| I have these three tables in SQL. I need to clean it up to send marketing a list by tomorrow1. rental.dbo is a list of customers who received a thank you card on November 18. 2. mail.dbo is a list of customers who either need to be eliminated because they have already received a card OR have not received a card yet. 3. custmailing.dbo is a list of customers who need to be eliminated completely, regardless of whether they have received a card or not.So essentially, all names from #3’s file need to be eliminated from #1’s file. Then all names from #1 need to be eliminated from #2. Please let me know if you need further explanation. These are the names of the coulumns. Customer Cust Adr1 Cust City Cust State Cust Zip STORE NUMBER/ED REP Item ClassI know it's going to be a join can someone help me dedupe statement? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-12-02 : 14:00:51
|
| This is the sample data for one of the databases. Each of them look like this but with diffrent customer information. Just want to delete the names from rental.dbo that are on custmailing.dbo (so the records in rental shouldn't have any names from custmailing.dbo)The list is really long but i shortened it just for sample dataCustomer Cust Adr1 Cust City Cust State Cust Zip STORE NUMBER/ED REP Item ClassBECKY WELLS 2167 JENNI LN WASHINGTON COURT HOUSE OH 43160-1689 Ed Rep #2954 CLARINETSTEVEN H ESKSTEIN 1208 BRAMBLE AVE WASHINGTON COURT HOUSE OH 43160-1086 Ed Rep #2954 ALTO SAXDAWN R DAWSON 728 E PAINT ST WASHINGTON COURT HOUSE OH 43160-1512 Ed Rep #2954 TRUMPETLOUIS LE 1338 BARCLAY DR CARROLLTON TX 75007-2870 Ed Rep #6954 VIOLAALICE BENWIDEZ 1737 E FRANKFORD RD APT 2304 CARROLLTON TX 75007-5613 Ed Rep #6954 VIOLALIEP NGUYEN 1403 HOMESTEAD LN CARROLLTON TX 75007-2952 Ed Rep #6954 VIOLASUZI DAVIS 3005 SIERRA DR CARROLLTON TX 75007-5646 Ed Rep #6954 VIOLINNANCY MORALES 3314 GREENGLEN CIR CARROLLTON TX 75007-2732 Ed Rep #6954 VIOLINJOSE ALBERTO GARCIA 3279 NORTHVIEW CARROLLTON TX 75007-3022 Ed Rep #6954 VIOLINSHAHIDA JIVANI 1120 MAC ARTHUR DR APT 1103 CARROLLTON TX 75007-4484 Music & Arts #6911 VIOLINLANICA M DORLEY 1820 E PETERS COLONY RD APT 3406 CARROLLTON TX 75007-3720 Ed Rep #6954 VIOLINELVIRA ORTEGA 1711 BIG CANYON TRL CARROLLTON TX 75007-5019 Ed Rep #6954 VIOLINDOUGLAS EDMEADS 1604 SAINT JAMES DR CARROLLTON TX 75007-2913 Ed Rep #6954 VIOLINTRESSA EDELMAN 3020 ROCKETT DR CARROLLTON TX 75007-5216 Ed Rep #6954 VIOLINSTEVE COUTOUMANOS 1649 BLACKSTONE DR CARROLLTON TX 75007-5122 Ed Rep #6954 VIOLINKIM GAMBINO 1827 WOODBURY CARROLLTON TX 75007-6105 Ed Rep #6954 VIOLINHAI TRAN 1501 SHONKA DR CARROLLTON TX 75007-2935 Ed Rep #6954 VIOLINSTACY HUNT 929 PLANTATION DR LEWISVILLE TX 75067-6120 Ed Rep #6954 VIOLINBRENDA OWENS 1810 CASTILLE DR CARROLLTON TX 75007-3014 Ed Rep #6954 VIOLINDESIREA MYERS 4250 LAVACA TRL CARROLLTON TX 75010-4025 Ed Rep #6954 VIOLINBELINDA CEPEDA 1414 MONACO DR 75067 LEWISVILLE TX 75067-5616 Ed Rep #6954 VIOLINMILTON HERNANDEZ 1820 E PETERS COLONY RD CARROLLTON TX 75007-3728 Ed Rep #6954 VIOLIN |
 |
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-12-02 : 14:20:42
|
| maybe something like this?? select customer, count(*) as myDupes, (select top 1 customer from dbo.custmail c2 where c2.customer=c1.customer)into #myTempTable from Mailer$ c1group by Customerhaving count(*) > 1 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-12-02 : 14:28:05
|
| These tables were excel sheetsWhen I imported them in it came with the names here are the insert scripts for each tableINSERT INTO [marketing].[dbo].[custmail] ([Customer] ,[Cust Adr1] ,[Cust City] ,[Cust State] ,[Cust Zip] ,[Store Number/Ed Rep] ,[Item Class]) VALUES (<Customer, nvarchar(255),> ,<Cust Adr1, nvarchar(255),> ,<Cust City, nvarchar(255),> ,<Cust State, nvarchar(255),> ,<Cust Zip, nvarchar(255),> ,<Store Number/Ed Rep, float,> ,<Item Class, nvarchar(255),>)GOINSERT INTO [marketing].[dbo].[Mailer$] ([Customer] ,[Cust Adr1] ,[Cust City] ,[Cust State] ,[Cust Zip] ,[Store Number/Ed Rep] ,[Item Class]) VALUES (<Customer, nvarchar(255),> ,<Cust Adr1, nvarchar(255),> ,<Cust City, nvarchar(255),> ,<Cust State, nvarchar(255),> ,<Cust Zip, nvarchar(255),> ,<Store Number/Ed Rep, float,> ,<Item Class, nvarchar(255),>)GOINSERT INTO [marketing].[dbo].[Rental$] ([Customer] ,[Cust Adr1] ,[Cust City] ,[Cust State] ,[Cust Zip] ,[STORE NUMBER/ED REP] ,[Item Class]) VALUES (<Customer, nvarchar(255),> ,<Cust Adr1, nvarchar(255),> ,<Cust City, nvarchar(255),> ,<Cust State, nvarchar(255),> ,<Cust Zip, nvarchar(255),> ,<STORE NUMBER/ED REP, nvarchar(255),> ,<Item Class, nvarchar(255),>)GOAll i want in the end is one table with the results for the remaining data. The output should be a new table which has no names from the custmail table and also no names from the mail table. the output should be the reamining data left over after those two changes |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-12-02 : 14:28:13
|
| does that make sense? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-12-03 : 08:40:33
|
| I really don't understnad how to do that! |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-12-03 : 08:50:50
|
| since i can't do that can you atleast help me to tell me if this statement is right or wrong? i think i missed something?SELECT a.Customer,a.[Cust Adr1] adr1, b.Customer,b.[Cust Adr1], c.Customer, c.[Cust Adr1], Count(*) as mydupes into #mytemptable FROM dbo.custmail a INNER JOIN dbo.Mailer$ b ON a.Customer = b.Customer INNER JOIN dbo.Rental$ c ON b.Customer = c.Customer group by a.Customer,a.[Cust Adr1] having COUNT(*) > 1 |
|
|
|
|
|
|
|
|
dedupe data