Establishing Precedence In ORDERBY Condition Causing Problems.

[pbd22]

Hi.

I really need some advice on fine-tuning a stored procedure
that is the meat of the search logic on my site. Customers
are allowed to save searches, which dumps the search logic
in a table called SavedSearches for later access to the search.

My problem started with the ORDERBY condition used for zipcode
searches. The condition did something like:
"order by CASE WHEN userID=67 THEN 1 WHEN userID=103 THEN 2 WHEN
userID=102 THEN 3 WHEN userID=81 THEN 4"
Of course, this fails when a customer described in the saved search
results deletes his profile.

I have since attempted to brace against this problem by adding a
UserPrecendence table with the following columns: email_address,
up_order (or, user precedence order), and userID.

Since I have made the precedence changes, I have been unsuccessful in
getting any results (data) back from the query. I think it has to do
with the change but am not quite sure what I am doing wrong.

I would appreciate it is somebody could take a look at my sproc with
particular attention to how precedence is handled in the ORDERBY
condition. Maybe you can see something I can not?

As always, much appreciated.

PS - In addition to the UserPrecedence change, I have attempted to add
paging - returning N amount of pages per request based on passed-in
paramaters. I'd appreciate it if you could take a quick glance here
also just to make sure my logic is OK.

----------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[sp_PeopleSearch]
@pagenum INT = 1,
@perpage INT = 10
AS
BEGIN
SET NOCOUNT ON

DECLARE
@ubound INT,
@lbound INT,
@pages INT,
@rows INT

SELECT
@rows = COUNT(*),
@pages = COUNT(*) / @perpage
FROM
(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from CustomerPhotos with(nolock) union select distinct email_address
from EditProfile with(nolock) union select distinct email_address
from SavedSearches with(nolock) union select distinct email_address
from UserPrecedence with(nolock) union select distinct email_address
from RecentLogin with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join RecentLogin tab4 on (drv.emailAddress =
tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)

IF @rows % @perpage != 0 SET @pages = @pages + 1
IF @pagenum @pages SET @pagenum = @pages
IF @pagenum < 1 SET @pagenum = 1
SET @ubound = @perpage * @pagenum
SET @lbound = @ubound - (@perpage - 1)

SELECT

CurrentPage = @pagenum,
PageSize = @perpage,
TotalPages = @pages,
TotalRows = @rows,
UpperBoundary = @ubound,
LowerBoundary = @lbound

-- this method determines the string values
-- for the first desired row, then sets the
-- rowcount to get it, plus the next n rows

DECLARE

@gender VARCHAR(50),
@country VARCHAR(50),
@orderby INTEGER,
@low VARCHAR(50),
@high VARCHAR(50),
@photo VARCHAR(50),
@sort INTEGER

SET ROWCOUNT @lbound

SELECT

@gender = saved_sex,
@country = saved_country,
@orderby = saved_orderby,
@low = saved_fage,
@high = saved_tage,
@sort = saved_sort,
@photo = saved_photo_string

FROM

(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from CustomerPhotos with(nolock) union select distinct email_address
from EditProfile with(nolock) union select distinct email_address
from SavedSearches with(nolock) union select distinct email_address
from UserPrecedence with(nolock) union select distinct email_address
from RecentLogin with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join RecentLogin tab4 on (drv.emailAddress =
tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)

ORDER BY CASE @sort

WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order

END DESC

SET ROWCOUNT @perPage

SELECT COALESCE
(
tab1.emailAddress,
tab2.user_name,
tab3.email_address,
tab4.email_address,
tab5.email_address,
tab6.email_address
)
id ,
tab1.bday_day ,
tab1.bday_month ,
tab1.bday_year ,
tab1.gender ,
tab1.zipCode ,
tab1.siteId ,
tab1.userID ,
tab2.photo_location ,
tab2.photo_name ,
tab2.photo_default ,
tab2.no_photo ,
tab3.headline ,
tab3.about_me ,
tab4.login_date ,
tab4.login_isonline,
tab5.up_order,
tab6.saved_orderby,
tab6.saved_sort,
tab6.saved_fage,
tab6.saved_tage

FROM

(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from CustomerPhotos with(nolock) union select distinct email_address
from EditProfile with(nolock) union select distinct email_address
from SavedSearches with(nolock) union select distinct email_address
from UserPrecedence with(nolock) union select distinct email_address
from RecentLogin with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join RecentLogin tab4 on (drv.emailAddress =
tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)

WHERE

tab1.gender = @gender
AND tab1.country = @country
AND tab1.bday_year BETWEEN @low AND @high
AND tab2.photo_default = 1 + @photo

--and not tab2.no_photo = 1
--firstName + '~' + lastName
-->= @fname + '~' + @lname

ORDER BY CASE @sort

WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order

END DESC

SET ROWCOUNT 0

END

[Erland Sommarskog]

pbd22 (du*****@gmail.com) writes:

I really need some advice on fine-tuning a stored procedure
that is the meat of the search logic on my site. Customers
are allowed to save searches, which dumps the search logic
in a table called SavedSearches for later access to the search.

My problem started with the ORDERBY condition used for zipcode
searches. The condition did something like:
"order by CASE WHEN userID=67 THEN 1 WHEN userID=103 THEN 2 WHEN
userID=102 THEN 3 WHEN userID=81 THEN 4"
Of course, this fails when a customer described in the saved search
results deletes his profile.

I have since attempted to brace against this problem by adding a
UserPrecendence table with the following columns: email_address,
up_order (or, user precedence order), and userID.

Since I have made the precedence changes, I have been unsuccessful in
getting any results (data) back from the query. I think it has to do
with the change but am not quite sure what I am doing wrong.

I would appreciate it is somebody could take a look at my sproc with
particular attention to how precedence is handled in the ORDERBY
condition. Maybe you can see something I can not?

I don't know your tables, but the procedure looks funny. From your
description it sounds like the query would return different results
depening on who is running it, or at least in different order, but
I can't work out how that should happen.

A few more comments:

ALTER PROCEDURE [dbo].[sp_PeopleSearch]

The sp_ prefix is reserved for system procedures, and SQL Server first
looks for these procedures in master. Don't use it for your own code.

(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from CustomerPhotos with(nolock) union select distinct email_address
from EditProfile with(nolock) union select distinct email_address
from SavedSearches with(nolock) union select distinct email_address
from UserPrecedence with(nolock) union select distinct email_address
from RecentLogin with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join RecentLogin tab4 on (drv.emailAddress =
tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)

There is a left join followed by an inner join, which refers back to
the table in the left join. If the first LEFT JOIN is there for a
reason, you convert it to an inner join here.

SELECT

@gender = saved_sex,
@country = saved_country,
@orderby = saved_orderby,
@low = saved_fage,
@high = saved_tage,
@sort = saved_sort,
@photo = saved_photo_string
...
ORDER BY CASE @sort

WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order

You haven't assigned @sort yet, so what does do in the ORDER BY
clause. And why do you have the same WHERE clause here is when you
do the count and return the data. What is this supposed to achieve?
By the way, which version of SQL Server are you using?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pbd22]

On Jun 11, 3:15 pm, Erland Sommarskog <[e-mail]:

pbd22 ([e-mail]) writes:

I really need some advice on fine-tuning a stored procedure
that is the meat of the search logic on my site. Customers
are allowed to save searches, which dumps the search logic
in a table called SavedSearches for later access to the search.

My problem started with the ORDERBY condition used for zipcode
searches. The condition did something like:
"order by CASE WHEN userID=67 THEN 1 WHEN userID=103 THEN 2 WHEN
userID=102 THEN 3 WHEN userID=81 THEN 4"
Of course, this fails when a customer described in the saved search
results deletes his profile.

I have since attempted to brace against this problem by adding a
UserPrecendence table with the following columns: email_address,
up_order (or, user precedence order), and userID.

Since I have made the precedence changes, I have been unsuccessful in
getting any results (data) back from the query. I think it has to do
with the change but am not quite sure what I am doing wrong.

I would appreciate it is somebody could take a look at my sproc with
particular attention to how precedence is handled in the ORDERBY
condition. Maybe you can see something I can not?

I don't know your tables, but the procedure looks funny. From your
description it sounds like the query would return different results
depening on who is running it, or at least in different order, but
I can't work out how that should happen.

A few more comments:

ALTER PROCEDURE [dbo].[sp_PeopleSearch]

The sp_ prefix is reserved for system procedures, and SQL Server first
looks for these procedures in master. Don't use it for your own code.

(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from CustomerPhotos with(nolock) union select distinct email_address
from EditProfile with(nolock) union select distinct email_address
from SavedSearches with(nolock) union select distinct email_address
from UserPrecedence with(nolock) union select distinct email_address
from RecentLogin with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join RecentLogin tab4 on (drv.emailAddress =
tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)

There is a left join followed by an inner join, which refers back to
the table in the left join. If the first LEFT JOIN is there for a
reason, you convert it to an inner join here.

SELECT

@gender = saved_sex,
@country = saved_country,
@orderby = saved_orderby,
@low = saved_fage,
@high = saved_tage,
@sort = saved_sort,
@photo = saved_photo_string
...
ORDER BY CASE @sort

WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order

You haven't assigned @sort yet, so what does do in the ORDER BY
clause. And why do you have the same WHERE clause here is when you
do the count and return the data. What is this supposed to achieve?

By the way, which version of SQL Server are you using?

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks Erland.
I am too pooped to digest your comments tonight. I'll try a read
before work tomorrow. Thanks for responding. Much appreciated!

[pbd22]

Hi Erland,

OK. thanks for your reply.

Well, I am guessing you are confused from the "different results based
on
different users" nature of my question vs. what the SPROC is telling
you
because the way I have it set up is that the acutual saved query is
stored
as a string in a database table. The string gets saved and called at a
later
time when the user wants to use that particular search. An example
stored
search looks like the below (sorry for the code dump, but its for
illustration) :

select coalesce (tab1.emailAddress, tab2.user_name,
tab3.email_address, tab4.email_address) id , tab1.bday_day ,
tab1.bday_month , tab1.bday_year , tab1.gender , tab1.zipCode ,
tab1.siteId , tab1.userID , tab2.photo_location , tab2.photo_name ,
tab2.photo_default , tab2.no_photo , tab3.headline , tab3.about_me ,
tab4.login_date from ( select distinct emailAddress from Users union
select distinct user_name from PersonalPhotos union select distinct
email_address from EditProfile union select distinct email_address
from LastLogin ) drv Left Join Users tab1 on (drv.emailAddress =
tab1.emailAddress) Left Join PersonalPhotos tab2 on (drv.emailAddress
= tab2.user_name) Left Join LastLogin tab4 on (drv.emailAddress =
tab4.email_address) Left Join EditProfile tab3 on (drv.emailAddress =
tab3.email_address) where tab2.photo_default = 1 and tab2.no_photo = 1
order by tab1.registerDate ;

This method has been working for me except for when the WHERE clasuse
is
describing a zipcode search. In this case the ORDERBY conditions need
to describe each individual user and can be quite long. And, when an
individual user deletes his profile, a whole saved search can fail.
So, I created the UserPrecedence table that describes the ordered
list. Users can be deleted from the UserPrecedence table when they
remove themselves from the system.

So, now, ORDERBY registerDate, login_date, edit_date, or the zipcode
CASE statement is now handled by:

ORDER BY CASE @sort

where @sort represents 1,2,3, or 4 corresponding to each of the above
conditions.

You haven't assigned @sort yet, so what does it do in the ORDER BY
clause? And why do you have the same WHERE clause here as when you
do the count and return the data. What is this supposed to achieve?

The ORDER BY CASE @sort is supposed to only tell SQL to return data
based on the user's prefer'd search condition (registerDate,
edit_date, etc) and do it once. Since I have made the UserPrecedence
addition and attempted to figure out how to add paging to my results,
I have made a number of changes to my procedure and am no longer
getting predictable/reliable results (when I get results at all). If
you see some obvious errors, I'd appreciate change suggestions as
I am a bit over my head at this point.

By the way, which version of SQL Server are you using?

I was SQL Server 2000 when I wrote this SPROC but we have since
upgraded to SQL Express.

I hope I have answered your questions. Let me know if you have others.
I appreciate your help/suggestions.

Regards,
Peter

On Jun 11, 3:15 pm, Erland Sommarskog <[e-mail]:

pbd22 ([e-mail]) writes:

I really need some advice on fine-tuning a stored procedure
that is the meat of the search logic on my site. Customers
are allowed to save searches, which dumps the search logic
in a table called SavedSearches for later access to the search.

My problem started with the ORDERBY condition used for zipcode
searches. The condition did something like:
"order by CASE WHEN userID=67 THEN 1 WHEN userID=103 THEN 2 WHEN
userID=102 THEN 3 WHEN userID=81 THEN 4"
Of course, this fails when a customer described in the saved search
results deletes his profile.

I have since attempted to brace against this problem by adding a
UserPrecendence table with the following columns: email_address,
up_order (or, user precedence order), and userID.

Since I have made the precedence changes, I have been unsuccessful in
getting any results (data) back from the query. I think it has to do
with the change but am not quite sure what I am doing wrong.

I would appreciate it is somebody could take a look at my sproc with
particular attention to how precedence is handled in the ORDERBY
condition. Maybe you can see something I can not?

I don't know your tables, but the procedure looks funny. From your
description it sounds like the query would return different results
depening on who is running it, or at least in different order, but
I can't work out how that should happen.

A few more comments:

ALTER PROCEDURE [dbo].[sp_PeopleSearch]

The sp_ prefix is reserved for system procedures, and SQL Server first
looks for these procedures in master. Don't use it for your own code.

(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from CustomerPhotos with(nolock) union select distinct email_address
from EditProfile with(nolock) union select distinct email_address
from SavedSearches with(nolock) union select distinct email_address
from UserPrecedence with(nolock) union select distinct email_address
from RecentLogin with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join RecentLogin tab4 on (drv.emailAddress =
tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)

There is a left join followed by an inner join, which refers back to
the table in the left join. If the first LEFT JOIN is there for a
reason, you convert it to an inner join here.

SELECT

@gender = saved_sex,
@country = saved_country,
@orderby = saved_orderby,
@low = saved_fage,
@high = saved_tage,
@sort = saved_sort,
@photo = saved_photo_string
...
ORDER BY CASE @sort

WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order

You haven't assigned @sort yet, so what does do in the ORDER BY
clause. And why do you have the same WHERE clause here is when you
do the count and return the data. What is this supposed to achieve?

By the way, which version of SQL Server are you using?

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

pbd22 (du*****@gmail.com) writes:

The string gets saved and called at a later time when the user wants to
use that particular search. An example stored search looks like the
below (sorry for the code dump, but its for illustration) :

That query looks very much like the SELECT in the procedure you
posted?

>You haven't assigned @sort yet, so what does it do in the ORDER BY
clause? And why do you have the same WHERE clause here as when you
do the count and return the data. What is this supposed to achieve?

The ORDER BY CASE @sort is supposed to only tell SQL to return data
based on the user's prefer'd search condition (registerDate,
edit_date, etc) and do it once. Since I have made the UserPrecedence
addition and attempted to figure out how to add paging to my results,
I have made a number of changes to my procedure and am no longer
getting predictable/reliable results (when I get results at all). If
you see some obvious errors, I'd appreciate change suggestions as
I am a bit over my head at this point.

The particular query I asked about was:

SELECT

@gender = saved_sex,
@country = saved_country,
@orderby = saved_orderby,
@low = saved_fage,
@high = saved_tage,
@sort = saved_sort,
@photo = saved_photo_string
...
ORDER BY CASE @sort

WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order

The ORDER BY CASE @sort here is meaningless, since at this point @sort
has the value NULL. You answered my question what this CASE @sort was
supposed to achieve by talking about returning data. But you are not
returning data. You are assigning variables.

But the ORDER BY is probably the least strange about this SELECT. As
far as I can call you have <bigquerythrice in your procedure:

1) SELECT Rows = COUNT(*), Pages = COUNT(*) / @pagesize
FROM <bigquery>

2) SELECT @country = saved_country, @sort = saved_sort, ...
FROM <bigquery>

3) SELECT <cols to clientFROM <bigquery>

1) and 3) makes perfect sense. The second I cannot understand. As far
as I understand, this query is likely to return multiple rows. But which
rows it returns - we don't know. Since @sort is NULL at this point,
the ORDER BY has no effect. It's probably the explanation to why your @sort
goes bad, but I can't say what you should do to correct, because I have very
little clue how your tables are related.

But what I would expect is that you would first read a single row from
the SavedSearches table. But now you seem to include that table in every
query, which seems funny to me - but I very little what this is all about.

>By the way, which version of SQL Server are you using?

I was SQL Server 2000 when I wrote this SPROC but we have since
upgraded to SQL Express.

In such case, replace SET ROWCOUNT with SELECT TOP(@rowsize).
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

pbd22 (du*****@gmail.com) writes:

OK. I have changed the procedure significantly to use the Row_Number()
method in SQL 2005 for paging.

In this procedure, I am trying to do the following:

1) used the passed-in parameters to figure out which saved search we
are using.
2) query the SavedSearch table to populate the local parameters with
the saved values
3) create a temporary table that is sorted against the local
paramerters.

I am having problems figuring out how to create this temporary table.
At a quick glance, does the "SELECT COALESCE" statement seem
like it has been logically placed or does it seem out of place? I keep
getting

"Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ')'. "

This is because you don't have an alias does the derived table. Add
"AS x" before the faulty parenthesis.

Also, you are missing a semi-colon before ';'

I can't seem to build the temp table without errors.

I can't even see a temp-table. I can see a common table expression,
is that you are thinking of?

At a quick glance does the logic in this procedure seem to make sense?

Since you apparently haven't tested the code yet, I don't feel compelled
to make a thorough review. But:

SET @saveddate = (SELECT saved_date FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @savedname = (SELECT saved_name FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
...

It would be more effecient and less verbose with:

SELECT @saveddate = saved_date, @savedname = saved_name, ...
FROM SavedSearches
WHERE search_name=@searchname
AND email_address=@emailaddy

Really what the CTE that returns a single column is supposed to mean,
I don't know, but I guess that you find out when you test what you
really intended.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

pbd22 (du*****@gmail.com) writes:

I am wondering if we are talking about the same "SELECT DISTINCT"?

Yes, we are. The point of my philosophical discussion was that you
should get rid of the duplicates by writing your joins better or
refine the data model.

But as I still don't know what your query is supposed to achieve, I can't
really say how you would do that.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pbd22]

On Jul 20, 2:26 pm, Erland Sommarskog <[e-mail]:

pbd22 ([e-mail]) writes:

I am wondering if we are talking about the same "SELECT DISTINCT"?

Yes, we are. The point of my philosophical discussion was that you
should get rid of the duplicates by writing your joins better or
refine the data model.

But as I still don't know what your query is supposed to achieve, I can't
really say how you would do that.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi Erland,

Thanks. I have sent you an email. I'll check back here for a
continuation of the thread.

Thanks again.