Union returns duplicates

Question

Union returns duplicates

bevans 97934

SSC Rookie

Points: 30
More actions
January 7, 2012 at 4:36 am

#254301

This SQL is meant to show the changes that will be made, when removing a selected user's email address from a batch.
However, when executed, each row is duplicated, and in the duplication, the semi-colon or comma isn't removed.
For example, if I wanted to remove user "sam@mail.com"
The table results displayed would be:
Row 1:
BatchID: 50
ParamName:EmailTo
ParamValue: jack@mail.com;sam@mail.com;frank@mail.com
NewParamValue: jack@mail.com;frank@mail.com
Row 2:
BatchID: 50
ParamName:EmailTo
ParamValue: jack@mail.com;sam@mail.com;john@mail.com
NewParamValue: jack@mail.com;;frank@mail.com
Ideally, it should only display each row once, and not have the semicolon error.
It seems to be a union error, because when I comment out the First and second union statements, it runs fine.
-- Delete email address from a.Batch
IF(@EmailAddress IS NOT NULL)
BEGIN
IF(LEN(@EmailAddress) > 0)
BEGIN
IF(@ShowOnly = 1)
BEGIN
SELECT DISTINCT BatchID, Name ParamName, Value ParamValue, NewParamValue
FROM (
SELECT *, REPLACE(VALUE, @EmailAddress + ',', '') NewParamValue
FROM a.Batch
WHERE VALUE LIKE '%' + @EmailAddress + ',' + '%'
UNION
SELECT *, REPLACE(VALUE, @EmailAddress + ';', '') NewParamValue
FROM a.Batch
WHERE VALUE LIKE '%' + @EmailAddress + ';' + '%'
UNION
SELECT *, REPLACE(VALUE, @EmailAddress, '') NewParamValue
FROM a.Batch
WHERE VALUE LIKE '%' + @EmailAddress + '%'
) emails
END
Any help is much appreciated

Viewing 8 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Could you post table designs and some sample (easily insertable) data so that we can try potential solutions out?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bevans 97934 SSC Rookie Points: 30 More actions · Answer 2

I unfortunately can only access the database again on Monday.

I'm not sure how much of it you understood, but someone suggested that there is no need at all for a union.

That it could be done like this:

SELECT *, REPLACE(REPLACE(REPLACE(VALUE, @EmailAddress + ',', ''),@EmailAddress + ';',''),@EmailAddress,'') NewParamValue

FROM a.Batch

WHERE VALUE LIKE '%' + @EmailAddress + '[,;]%'

OR VALUE LIKE '%' + @EmailAddress + '%'

Since the Union is used to combine the rows where the emails are separated by a comma, semicolon or nothing.

Would this code be effective?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Not sure. I'll be honest, I didn't fully understand the requirements or problem, that's why I want some data to play with

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bevans 97934 SSC Rookie Points: 30 More actions · Answer 4

Sorry about that. I'll post the full excerpt of code for you

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

No worries, it just makes things easier to test and means you'll get something that does work, not something we think works.

p.s. Do you have a tally/numbers table in the database? If not, would creating one be an option?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bevans 97934 SSC Rookie Points: 30 More actions · Answer 6

I will be able to give the sample data and all that's requested, only on Monday though, when I have access to the database again...

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 7

bevans 97934 (1/7/2012)
I unfortunately can only access the database again on Monday.
I'm not sure how much of it you understood, but someone suggested that there is no need at all for a union.
That it could be done like this:
SELECT *, REPLACE(REPLACE(REPLACE(VALUE, @EmailAddress + ',', ''),@EmailAddress + ';',''),@EmailAddress,'') NewParamValue
FROM a.Batch
WHERE VALUE LIKE '%' + @EmailAddress + '[,;]%'
OR VALUE LIKE '%' + @EmailAddress + '%'
Since the Union is used to combine the rows where the emails are separated by a comma, semicolon or nothing.
Would this code be effective?

You might need the union in case there is no email replaced, because your first "WHERE" clause will be false.

DECLARE @EmailAddress VARCHAR(100)

SET @EmailAddress = 'JOE@mail.com'

SELECT VALUE, REPLACE(REPLACE(REPLACE(VALUE, @EmailAddress + ',', ''),@EmailAddress + ';',''),@EmailAddress,'') NewParamValue

FROM

(SELECT 'jack@mail.com;sam@mail.com;john@mail.com' AS VALUE) Batch

WHERE VALUE LIKE '%' + @EmailAddress + '[,;]%'

OR VALUE LIKE '%' + @EmailAddress + '%'

UNION

SELECT VALUE, VALUE NewParamValue FROM

(SELECT 'jack@mail.com;sam@mail.com;john@mail.com' AS VALUE) Batch

WHERE VALUE NOT LIKE '%' + @EmailAddress + '[,;]%'

AND VALUE NOT LIKE '%' + @EmailAddress + '%'