Remove duplicates based on occurrences with join on other table

Question

Post reply

Remove duplicates based on occurrences with join on other table

Viewing 15 posts - 16 through 30 (of 37 total)

You must be logged in to reply to this topic. Login to reply

petervdkerk SSCrazy Points: 2155 More actions · Answer 1

Ok I did that...that deletes the data from prospectsincampaigns for the specific campaign, good!

However *looks afraid to ask*, it still deletes all data from prospects that are NOT in the current in the campaign.

If I specifiy a campaign I need the duplicates that are removed from prospects to be only the ones that also are in that specific campaign? How do I do that?

TomThomson SSC Guru Points: 104773 More actions · Answer 2

petervdkerk (8/1/2011)

Ok I did that...that deletes the data from prospectsincampaigns for the specific campaign, good!

However *looks afraid to ask*, it still deletes all data from prospects that are NOT in the current in the campaign.

If I specifiy a campaign I need the duplicates that are removed from prospects to be only the ones that also are in that specific campaign? How do I do that?

Never be afraid to ask. Just try to learn from the answers so that you don't have to ask the same question again (at least not too ofeten - learning first time is not always easy).

It's late at night here, and I'm too tired to check this thoroughly or even to understand it properly, but as I see it there are two possible ways of doing this. Both involve modifying the same block of code, which is the block reading



--delete redundant prospects rows

delete p from prospects p

where not exists (select * from ProspectsInCampaigns where prospectid = id)

and exists (select * from ProspectsInCampaigns C inner join prospects p2

on p2.id = c.prospectid where p2.companyname = p.companyname)

I'm not sure exactly which of the two possibilities actually matches the requirement: the two possibilities are (1) delete that block of code altogether, and (2) instead of deleting it make it less eager to delete by adding "and c.campaignid = 4" (or whatever the campaignid you are dealing with is) to the predicate for the exists condition. If neither works, let me know. From your wording I think iyou probably want possibility (1), but not 100% sure.

Tom

petervdkerk SSCrazy Points: 2155 More actions · Answer 3

Ok, hopefully you had a good night sleep, because Im back 🙂

This is where I start from:

START

id companyname

1355132 Smeets M.B. Zuid-Limburg BV

1355133 F.J. ten Berge BV

1355134 F.J. ten Berge BV

1355135 MacDonalds

1355136 MacDonalds

1355137 Duran BV

1355138 Duran BV

1355139 Duran BV

1355140 Company in No campaign

prospectid campaignid result

1355132 1 0

1355133 1 0

1355134 1 0

1355135 5 0

1355135 6 0

1355136 5 0

1355136 6 0

1355137 1 0

1355138 1 0

1355139 4 0

1355139 4 8

1355139 4 14

1355139 6 15

Now I executed this statement (narrowing the delete statement):

-- work out which non-min prospectsincampaigns rows should be deleted

select c.prospectid, c.campaignid into #deletions

from dbo.ProspectsInCampaigns c, dbo.prospects p

WHERE result = 0 and p.id = c.prospectid

and exists (select * from dbo.ProspectsInCampaigns c2, dbo.prospects p2

where p.companyname = p2.companyname and c2.campaignid = c.campaignid

and c2.prospectID = p2.ID and p.id > p2.ID)

-- delete the propspectsincampaigns rows

delete c from dbo.ProspectsInCampaigns c where exists (select * from #deletions d

where d.prospectid = c.prospectid and d.campaignid = c.campaignid)

--delete redundant min prospectsincampaigns rows

delete c from ProspectsInCampaigns c, Prospects p

where result = 0 and p.id = c.prospectid

and exists (select * from ProspectsInCampaigns c2, Prospects p2

where c2.prospectID = p2.id and c2.campaignid = c.campaignID

and p2.companyname = p.companyname and c2.prospectID > c.prospectID)

--delete the prospects rows matching deleted prospectsincampaigns rows

delete p from dbo.Prospects p where exists (select * from #deletions d

where d.prospectID = p.id)

--delete redundant prospects rows

delete p from prospects p

where not exists (select * from ProspectsInCampaigns where prospectid = id and campaignid = 5)

and exists (select * from ProspectsInCampaigns C inner join prospects p2

on p2.id = c.prospectid where p2.companyname = p.companyname and c.campaignid = 5)

-- get rid of duplicate entries

;with findDups as

(select *, row_number() over (order by prospectID,campaignid,result) as N

from ProspectsInCampaigns)

delete f from findDups f join findDups f2

on f.prospectID=f2.prospectID and f.campaignid = f2.campaignid and f.campaignid = 5

where f.N < f2.N and f.result = 0

-- throw away temporary data

drop table #deletions

result is:

id companyname

1355132 Smeets M.B. Zuid-Limburg BV

1355133 F.J. ten Berge BV

1355135 MacDonalds

1355137 Duran BV

1355139 Duran BV

1355140 Company in No campaign

prospectid campaignid result

1355132 1 0

1355133 1 0

1355135 5 0

1355135 6 0

1355137 1 0

1355139 4 0

1355139 4 8

1355139 4 14

1355139 6 15

So you see, even though I (think I) narrowed it to canmpaignid=5, still the duplicate records from campaign 1 are removed from prospects, while these should actually remain intact.

Im talking about these:

id companyname

1355132 Smeets M.B. Zuid-Limburg BV

1355133 F.J. ten Berge BV

1355134 F.J. ten Berge BV

prospectid campaignid result

1355132 1 0

1355133 1 0

1355134 1 0

So, I also tried to remove the delete statement completely, like so:

-- work out which non-min prospectsincampaigns rows should be deleted

select c.prospectid, c.campaignid into #deletions

from dbo.ProspectsInCampaigns c, dbo.prospects p

WHERE result = 0 and p.id = c.prospectid

and exists (select * from dbo.ProspectsInCampaigns c2, dbo.prospects p2

where p.companyname = p2.companyname and c2.campaignid = c.campaignid

and c2.prospectID = p2.ID and p.id > p2.ID)

-- delete the propspectsincampaigns rows

delete c from dbo.ProspectsInCampaigns c where exists (select * from #deletions d

where d.prospectid = c.prospectid and d.campaignid = c.campaignid)

--delete redundant min prospectsincampaigns rows

delete c from ProspectsInCampaigns c, Prospects p

where result = 0 and p.id = c.prospectid

and exists (select * from ProspectsInCampaigns c2, Prospects p2

where c2.prospectID = p2.id and c2.campaignid = c.campaignID

and p2.companyname = p.companyname and c2.prospectID > c.prospectID)

--delete the prospects rows matching deleted prospectsincampaigns rows

delete p from dbo.Prospects p where exists (select * from #deletions d

where d.prospectID = p.id)

-- get rid of duplicate entries

;with findDups as

(select *, row_number() over (order by prospectID,campaignid,result) as N

from ProspectsInCampaigns)

delete f from findDups f join findDups f2

on f.prospectID=f2.prospectID and f.campaignid = f2.campaignid and f.campaignid = 5

where f.N < f2.N and f.result = 0

-- throw away temporary data

drop table #deletions

But that still deletes duplicate prospects that are in another campaign than campaign 5 in this case.

TomThomson SSC Guru Points: 104773 More actions · Answer 4

petervdkerk (8/2/2011)

So you see, even though I (think I) narrowed it to canmpaignid=5, still the duplicate records from campaign 1 are removed from prospects, while these should actually remain intact.

I think you misunderstood me: what I suggested was meant as an addition to the narrowing you had already done, not a substitute for it.

Tom

petervdkerk SSCrazy Points: 2155 More actions · Answer 5

petervdkerk

SSCrazy

Points: 2155

August 3, 2011 at 8:28 am

#1363092

I'm not sure what you mean there.

If you talk about the fact that I substituted campaignid=4 with campaignid=5, thats only because I was testing the code on another campaign. If this substitution is NOT what you mean, in that case I dont know what you mean I should be adding instead of substituting.

TomThomson SSC Guru Points: 104773 More actions · Answer 6

petervdkerk (8/3/2011)

I'm not sure what you mean there.

If you talk about the fact that I substituted campaignid=4 with campaignid=5, thats only because I was testing the code on another campaign. If this substitution is NOT what you mean, in that case I dont know what you mean I should be adding instead of substituting.

On Saturday you said

One more thing: Im now also trying to narrow it all down to a single campaign (4 in this case), so I added " and c.campaignid=4" on several lines, but that doesnt seem to work...what should I add to achieve that?

and the code you posted then has "and c.campaignid = 4" in 3 places.

Your latest code should have had "and c.campaign = 5" in those three places, but it doesn't - it has nothing there. Adding to those 3 narrowings, there should have been the extra thing I suggested - but you have only the extra thing. In effect you had substituted one narrowing clause for the original three, instead of adding that one to the original three to end up with four.

Tom

petervdkerk SSCrazy Points: 2155 More actions · Answer 7

petervdkerk

SSCrazy

Points: 2155

August 3, 2011 at 11:41 am

#1363251

Ok, maybe Im not understanding you correctly, but in my last post I posted the 2 different options you gave me and the way I interpreted them.

So there are 2(!) different statement and in the top statement I have 3 times: "and campaignid=5".

Or is that not what you meant?

Thanks again! 🙂

TomThomson SSC Guru Points: 104773 More actions · Answer 8

petervdkerk (8/3/2011)

Ok, maybe Im not understanding you correctly, but in my last post I posted the 2 different options you gave me and the way I interpreted them.

So there are 2(!) different statement and in the top statement I have 3 times: "and campaignid=5".

Or is that not what you meant?

Thanks again! 🙂

OK, the code in your last post begin like this:



select c.prospectid, c.campaignid into #deletions

from dbo.ProspectsInCampaigns c, dbo.prospects p

WHERE result = 0 and p.id = c.prospectid

and exists (select * from dbo.ProspectsInCampaigns c2, dbo.prospects p2

where p.companyname = p2.companyname and c2.campaignid = c.campaignid

and c2.prospectID = p2.ID and p.id > p2.ID)

That had "and c.campaignid = 4" in it in your earlier post, but doesn't have "and c.campaignid = 5" now. It should have that.

Then it goes



-- delete the propspectsincampaigns rows

delete c from dbo.ProspectsInCampaigns c where exists (select * from #deletions d

where d.prospectid = c.prospectid and d.campaignid = c.campaignid)

--delete redundant min prospectsincampaigns rows

delete c from ProspectsInCampaigns c, Prospects p

where result = 0 and p.id = c.prospectid

and exists (select * from ProspectsInCampaigns c2, Prospects p2

where c2.prospectID = p2.id and c2.campaignid = c.campaignID

and p2.companyname = p.companyname and c2.prospectID > c.prospectID)

when you posted on Saturday, you had "and c.campaignid = 4" on both those delete statements, but you now have "and c.campaignid = 5" on neither. It's probably only needed on the second one, but it would be sensible to put it on the first one too since that's what you had before.

The rest of the code you last posted is



--delete redundant prospects rows

delete p from prospects p

where not exists (select * from ProspectsInCampaigns where prospectid = id and campaignid = 5)

and exists (select * from ProspectsInCampaigns C inner join prospects p2

on p2.id = c.prospectid where p2.companyname = p.companyname and c.campaignid = 5)

-- get rid of duplicate entries

;with findDups as

(select *, row_number() over (order by prospectID,campaignid,result) as N

from ProspectsInCampaigns)

delete f from findDups f join findDups f2

on f.prospectID=f2.prospectID and f.campaignid = f2.campaignid and f.campaignid = 5

where f.N < f2.N and f.result = 0

-- throw away temporary data

drop table #deletions

which ought to work (I miscounted the occurrences of campaigneid=5 in my last reply, which may have confused you.)

Tom

petervdkerk SSCrazy Points: 2155 More actions · Answer 9

I ended up with this statement:

-- work out which non-min prospectsincampaigns rows should be deleted

select c.prospectid, c.campaignid into #deletions

from dbo.ProspectsInCampaigns c, dbo.prospects p

WHERE result = 0 and p.id = c.prospectid

and exists (select * from dbo.ProspectsInCampaigns c2, dbo.prospects p2

where p.companyname = p2.companyname and c2.campaignid = c.campaignid and c.campaignid=5

and c2.prospectID = p2.ID and p.id > p2.ID)

-- delete the propspectsincampaigns rows

delete c from dbo.ProspectsInCampaigns c where exists (select * from #deletions d

where d.prospectid = c.prospectid and d.campaignid = c.campaignid and c.campaignid=5)

--delete redundant min prospectsincampaigns rows

delete c from ProspectsInCampaigns c, Prospects p

where result = 0 and p.id = c.prospectid

and exists (select * from ProspectsInCampaigns c2, Prospects p2

where c2.prospectID = p2.id and c2.campaignid = c.campaignID and c.campaignid=5

and p2.companyname = p.companyname and c2.prospectID > c.prospectID)

--delete the prospects rows matching deleted prospectsincampaigns rows

delete p from dbo.Prospects p where exists (select * from #deletions d

where d.prospectID = p.id)

--delete redundant prospects rows

delete p from prospects p

where not exists (select * from ProspectsInCampaigns where prospectid = id and campaignid = 5)

and exists (select * from ProspectsInCampaigns C inner join prospects p2

on p2.id = c.prospectid where p2.companyname = p.companyname and c.campaignid = 5)

-- get rid of duplicate entries

;with findDups as

(select *, row_number() over (order by prospectID,campaignid,result) as N

from ProspectsInCampaigns)

delete f from findDups f join findDups f2

on f.prospectID=f2.prospectID and f.campaignid = f2.campaignid and f.campaignid = 5

where f.N < f2.N and f.result = 0

-- throw away temporary data

drop table #deletions

Which almost does what I need.

The final problem now is that the duplicates removed from prospects could still be in another campaign.

In my case, when deleting duplicates from campaign 5, those duplicates are still in campaign 1. So the prospectid in prospectsincampaigns with campaignid 1 now points to a non-existing record in prospects.

How can I prevent that? Seems to me an additional check is needed that checks if the deleted dupes do not occur in another campaign than the campaign that is currently being handled, campaign 5 in my case.

And just out of curiosity: are you an SSC employee or just a (really really) active SQL fanatic (or both ;). Anyways: thanks again for your time and help!

TomThomson SSC Guru Points: 104773 More actions · Answer 10

petervdkerk (8/3/2011)

Which almost does what I need.

The final problem now is that the duplicates removed from prospects could still be in another campaign.

In my case, when deleting duplicates from campaign 5, those duplicates are still in campaign 1. So the prospectid in prospectsincampaigns with campaignid 1 now points to a non-existing record in prospects.

How can I prevent that? Seems to me an additional check is needed that checks if the deleted dupes do not occur in another campaign than the campaign that is currently being handled, campaign 5 in my case.

I should have thought of that - my excuse is that since you introduced the idea of restricting to one campaign, between trying to deal with phone company problems, hospital visits to unwell friends, an invalid wife, and various other little issues I've been a bit distracted and nothad time to do proper testing.

The two statements that delete prospects are



--delete the prospects rows matching deleted prospectsincampaigns rows

delete p from dbo.Prospects p where exists (select * from #deletions d

where d.prospectID = p.id)

--delete redundant prospects rows

delete p from prospects p

where not exists (select * from ProspectsInCampaigns where prospectid = id and campaignid = 5)

and exists (select * from ProspectsInCampaigns C inner join prospects p2

on p2.id = c.prospectid where p2.companyname = p.companyname and c.campaignid = 5)

Each needs a change, so that they become



--delete the prospects rows matching deleted prospectsincampaigns rows

delete p from dbo.Prospects p where exists (select * from #deletions d

where d.prospectID = p.id

and not exists (select * from prospectsincampaigns C where c.prospectid = p.id)

--delete redundant prospects rows

delete p from prospects p

where not exists (select * from ProspectsInCampaigns where prospectid = id and campaignid <> 5)

and exists (select * from ProspectsInCampaigns C inner join prospects p2

on p2.id = c.prospectid where p2.companyname = p.companyname and c.campaignid = 5)

And just out of curiosity: are you an SSC employee or just a (really really) active SQL fanatic (or both ;). Anyways: thanks again for your time and help!

The only SSC employee, as far as I know, is Steve Jones, who is employed by Redgate Software to run the SSC wesite and provide editorial material (and do other things for the company). Everyone else is either an SQL enthusiast who provides help voluntarily, or someone looking for help (that's how most people start out). Because several real experts are involved we all learn quite a bit too. Some of us also write articles for the site (the going rate is $25 per article, which gives Redgate exclusive rights to the article for 3 months and non-exclusive rights perpetually, if I recall correctly). I contribute a lot less, and help a lot fewer people, than the main contributors to the site (who are volunteers, not Redgate employees).

Tom

petervdkerk SSCrazy Points: 2155 More actions · Answer 11

You call that little issues? My respect to you sir, reminds me once again my life is actually pretty easy!

Ok, with the prospects and prospectsincampaigns tables, you can use the following statement to generate the exact testdata I'm using right now:

declare @newid int

INSERT INTO prospects(companyname,companyaddress,companyhousenr,companyhousenradd,companyzipcode,companycity,companyphone,cp_sex,cp_firstname,cp_middlename,cp_lastname,cp_email,cp_phone,telco_mob_connections,telco_mob_provider,telco_mob_enddate)

VALUES ('Smeets M.B. Zuid-Limburg BV','Handelsstraat','2','','6433 KB','Heerlen (MAASTRICHT)','0464209900','1','','','Laumans','n.laumans@smeets-mb.nl','','15','T-Mobile','2/1/2011')

set @newid=SCOPE_IDENTITY()