Union returns rows twice - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Union returns rows twice

Author

Topic

bevans
Starting Member

10 Posts

Posted - 2012-01-07 : 06:16:15

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-07 : 08:18:49

why should you need union here? wont this be enough?


SELECT *, REPLACE(REPLACE(REPLACE(VALUE, @EmailAddress + ',', ''),@EmailAddress + ';',''),@EmailAddress,'') NewParamValue
FROM a.Batch
WHERE VALUE LIKE '%' + @EmailAddress + '[,;]%'
OR  VALUE LIKE '%' + @EmailAddress + '%'

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bevans
Starting Member

10 Posts

Posted - 2012-01-07 : 10:07:21

I actually think that totally would work.
Thank you
I can only test this code on Monday, when I have access to the database.
I will keep you posted. Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-07 : 10:24:53

quote:
Originally posted by bevans

I actually think that totally would work.
Thank you
I can only test this code on Monday, when I have access to the database.
I will keep you posted. Thanks again.

thats fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bevans
Starting Member

10 Posts

Posted - 2012-01-09 : 04:04:10

quote:
Originally posted by visakh16

quote:
Originally posted by bevans

I actually think that totally would work.
Thank you
I can only test this code on Monday, when I have access to the database.
I will keep you posted. Thanks again.

thats fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks a lot. It fixed the problem and worked perfectly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 04:13:15

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources