Establishing Precedence In ORDERBY Condition Causing Problems.

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

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

pbd22 (dushkin@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

On Jun 11, 3:15 pm, Erland Sommarskog wrote:
> pbd22 (dush...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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!

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

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 wrote:
> pbd22 (dush...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

pbd22 (dushkin@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 thrice in your procedure:

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

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

3) SELECT FROM

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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

On Jun 13, 2:52 pm, Erland Sommarskog wrote:
> pbd22 (dush...@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 thrice in your procedure:
>
> 1) SELECT Rows = COUNT(*), Pages = COUNT(*) / @pagesize
> FROM
>
> 2) SELECT @country = saved_country, @sort = saved_sort, ...
> FROM
>
> 3) SELECT FROM
>
> 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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Hi Erland,

Thanks for your suggestions and apologies for the LONG pause.
I had to focus on another part of development for a bit and am now
back to trying to get my database programming and search design
correct.

To be honest, I know what I did made sense to me at the time, but
since I have tried to add paging and other features to my search
stored
procedure, I think it has gotten away from me.

Per your below comment:

> 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.

It sounds to me like the middle code block is causing me my errors but
I am
not sure what I am doing wrong still. Would you mind taking a look at
an Entity
Relationship Diagram? It might give you a better understanding of how
my data
is designed and for what purpose. If that is OK, I'll email it to you
via your address
provided here.

I *seriously* appreciate your feedback.

Regards,
Peter

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

pbd22 (dushkin@gmail.com) writes:
>> 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.
>
> It sounds to me like the middle code block is causing me my errors but
> I am not sure what I am doing wrong still. Would you mind taking a look
> at an Entity Relationship Diagram? It might give you a better
> understanding of how my data is designed and for what purpose. If that
> is OK, I'll email it to you via your address provided here.

And I don't know what you are doing wrong, because I don't know what you
are trying to achieve.

There is a common recommendation for this type of questions, and that is
that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The resired result given the sample.

Now, since your original query had some 7-8 tables whereof several repeated
in the FROM clause, you will need to simplify the problem down to the
core.

If I understand this correctly, this is about saved searches, so the
clou is certainly SavedSearches, but try to invent a similar case with
fewer tables. Yes, that may take you some time, but I rather have
you doing that than showing me an E-R diagramme that may not help me
to understand what you are trying to achieve. To wit, I am not sure
that you understand yourself. But if you spend some time with a simpler
case then maybe you get can get that understanding.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

On Jul 13, 3:04 pm, Erland Sommarskog wrote:
> pbd22 (dush...@gmail.com) writes:
> >> 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.
>
> > It sounds to me like the middle code block is causing me my errors but
> > I am not sure what I am doing wrong still. Would you mind taking a look
> > at an Entity Relationship Diagram? It might give you a better
> > understanding of how my data is designed and for what purpose. If that
> > is OK, I'll email it to you via your address provided here.
>
> And I don't know what you are doing wrong, because I don't know what you
> are trying to achieve.
>
> There is a common recommendation for this type of questions, and that is
> that you post:
>
> o CREATE TABLE statements for your tables.
> o INSERT statements with sample data.
> o The resired result given the sample.
>
> Now, since your original query had some 7-8 tables whereof several repeated
> in the FROM clause, you will need to simplify the problem down to the
> core.
>
> If I understand this correctly, this is about saved searches, so the
> clou is certainly SavedSearches, but try to invent a similar case with
> fewer tables. Yes, that may take you some time, but I rather have
> you doing that than showing me an E-R diagramme that may not help me
> to understand what you are trying to achieve. To wit, I am not sure
> that you understand yourself. But if you spend some time with a simpler
> case then maybe you get can get that understanding.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Thanks Erland.

OK, I have done what you said and reduced the tables used in the
search.
After much messing around with the stored procedure, I have figured
out that
by commenting out the following code (at the end of the procedure), I
can get
results:

WHERE

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

(and, the ORDERBY code is commented out as it depends on this code).

I have also found that if I leave any one of the above lines the code
again
fails. So, for some reason, @gender, @country, @low, @high, and @photo
are not getting passed appropriately.

This is where I am at the moment, I'll report back as progress is
made.
Comments always appreciated (if you see something I don't) along the
way.

Thanks again for your patience.
Peter

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

On Jul 15, 6:33 pm, pbd22 wrote:
> On Jul 13, 3:04 pm, Erland Sommarskog wrote:
>
>
>
> > pbd22 (dush...@gmail.com) writes:
> > >> 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.
>
> > > It sounds to me like the middle code block is causing me my errors but
> > > I am not sure what I am doing wrong still. Would you mind taking a look
> > > at an Entity Relationship Diagram? It might give you a better
> > > understanding of how my data is designed and for what purpose. If that
> > > is OK, I'll email it to you via your address provided here.
>
> > And I don't know what you are doing wrong, because I don't know what you
> > are trying to achieve.
>
> > There is a common recommendation for this type of questions, and that is
> > that you post:
>
> > o CREATE TABLE statements for your tables.
> > o INSERT statements with sample data.
> > o The resired result given the sample.
>
> > Now, since your original query had some 7-8 tables whereof several repeated
> > in the FROM clause, you will need to simplify the problem down to the
> > core.
>
> > If I understand this correctly, this is about saved searches, so the
> > clou is certainly SavedSearches, but try to invent a similar case with
> > fewer tables. Yes, that may take you some time, but I rather have
> > you doing that than showing me an E-R diagramme that may not help me
> > to understand what you are trying to achieve. To wit, I am not sure
> > that you understand yourself. But if you spend some time with a simpler
> > case then maybe you get can get that understanding.
>
> > --
> > Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx
>
> Thanks Erland.
>
> OK, I have done what you said and reduced the tables used in the
> search.
> After much messing around with the stored procedure, I have figured
> out that
> by commenting out the following code (at the end of the procedure), I
> can get
> results:
>
> WHERE
>
> tab1.gender = @gender
> AND tab1.country = @country
> AND tab1.bday_year BETWEEN @low AND @high
> AND tab2.photo_default = 1 + @photo--WHERE
>
> (and, the ORDERBY code is commented out as it depends on this code).
>
> I have also found that if I leave any one of the above lines the code
> again
> fails. So, for some reason, @gender, @country, @low, @high, and @photo
> are not getting passed appropriately.
>
> This is where I am at the moment, I'll report back as progress is
> made.
> Comments always appreciated (if you see something I don't) along the
> way.
>
> Thanks again for your patience.
> Peter

Hi Erland (or anybody else),

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 ')'. "

I can't seem to build the temp table without errors.
At a quick glance does the logic in this procedure seem to make sense?
Any "trained-eye" corrections would be very helpful.

thanks.



CREATE PROCEDURE tre_SavedSearch
-- passed-in parameters
@searchname VARCHAR(50) = null, -- The Name Of The User-Defined
Search
@emailaddy VARCHAR(50) = null, -- The ID (email) of the User
@PageNum INT = 1, -- The Starting Page
@PageSize INT = 10, -- The Number of Rows Per Page
@debug INT = 0 -- Debug Value

AS
BEGIN

SET NOCOUNT ON

-- first, we need to pull the saved values from the
-- SavedSearch table to understand what we are looking
-- for.

DECLARE
@saveddate VARCHAR(50),
@savedname VARCHAR(50),
@defaultsearch VARCHAR(50),
@sex VARCHAR(50),
@fromage VARCHAR(50),
@toage VARCHAR(50),
@country VARCHAR(50),
@miles VARCHAR(50),
@pictures VARCHAR(50),
@zipcode VARCHAR(50),
@sortID INT -- 1 = registration
-- 2 = recent changes
-- 3 = recent login
-- 4 = distance order

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)
SET @sex = (SELECT saved_sex FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @fromage = (SELECT saved_fage FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @toage = (SELECT saved_tage FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @country = (SELECT saved_country FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @miles = (SELECT saved_miles FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @pictures = (SELECT saved_pictures FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @zipcode = (SELECT saved_postal FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @sortID = (SELECT saved_sort FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)


WITH SavedSearch AS
(

SELECT ROW_NUMBER() OVER ( ORDER BY CASE @sortID

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

) AS RowNum

FROM (
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

FROM

(select distinct emailAddress
from Users with(nolock) union select distinct user_name
from PersonalPhotos 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 LastLogin with(nolock)) drv
Left Join Users tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on (tab5.UserID=tab1.UserID)
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)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)

WHERE

tab1.gender = @sex
AND tab1.country = @country
AND tab1.bday_year BETWEEN @fromage AND @toage
--AND tab2.photo_default = 1 + @photo

)

)

SELECT * FROM SavedSearch
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize

ORDER BY CASE @sortID

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

END
GO

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

pbd22 (dushkin@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

On Sun, 15 Jul 2007 18:33:19 -0700, pbd22 wrote:

(snip)
>OK, I have done what you said and reduced the tables used in the
>search.
>After much messing around with the stored procedure, I have figured
>out that
>by commenting out the following code (at the end of the procedure), I
>can get
>results:
>
>WHERE
>
>tab1.gender = @gender
>AND tab1.country = @country
>AND tab1.bday_year BETWEEN @low AND @high
>AND tab2.photo_default = 1 + @photo--WHERE
>
>(and, the ORDERBY code is commented out as it depends on this code).
>
>I have also found that if I leave any one of the above lines the code
>again
>fails. So, for some reason, @gender, @country, @low, @high, and @photo
>are not getting passed appropriately.

Hi Peter,

I found the stored procedure code in an earlier message in this thread.
I don't know how much you changed, so the following might or might not
apply.

Your query uses a lot of left (outer) joins. Are you sure that these
can't be inner joins?

The tab1 and tab2 tables are among the tables that are outer joined. By
adding a criterium in the WHERE clause, you effectively convert them to
inner joins - so you should either modify the query to use inner join
(improved readability and maintainability and probably better
performance as well), or move the filters to the ON part of the
appropriate JOIN clauses.

Note that I did not do a complete review of your code; it's too long for
that. Try to trim down the problem to a more simplified case that's
short enough for us to invest our time in, yet similar enough to your
real problem that you can translate our soultions back to your original
situation. If you're not able to simplify the problem, you should
probably hire a SQL developer to aide you with this issue.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

On Jul 18, 3:06 pm, Hugo Kornelis
wrote:
> On Sun, 15 Jul 2007 18:33:19 -0700, pbd22 wrote:
>
> (snip)
>
>
>
> >OK, I have done what you said and reduced the tables used in the
> >search.
> >After much messing around with the stored procedure, I have figured
> >out that
> >by commenting out the following code (at the end of the procedure), I
> >can get
> >results:
>
> >WHERE
>
> >tab1.gender = @gender
> >AND tab1.country = @country
> >AND tab1.bday_year BETWEEN @low AND @high
> >AND tab2.photo_default = 1 + @photo--WHERE
>
> >(and, the ORDERBY code is commented out as it depends on this code).
>
> >I have also found that if I leave any one of the above lines the code
> >again
> >fails. So, for some reason, @gender, @country, @low, @high, and @photo
> >are not getting passed appropriately.
>
> Hi Peter,
>
> I found the stored procedure code in an earlier message in this thread.
> I don't know how much you changed, so the following might or might not
> apply.
>
> Your query uses a lot of left (outer) joins. Are you sure that these
> can't be inner joins?
>
> The tab1 and tab2 tables are among the tables that are outer joined. By
> adding a criterium in the WHERE clause, you effectively convert them to
> inner joins - so you should either modify the query to use inner join
> (improved readability and maintainability and probably better
> performance as well), or move the filters to the ON part of the
> appropriate JOIN clauses.
>
> Note that I did not do a complete review of your code; it's too long for
> that. Try to trim down the problem to a more simplified case that's
> short enough for us to invest our time in, yet similar enough to your
> real problem that you can translate our soultions back to your original
> situation. If you're not able to simplify the problem, you should
> probably hire a SQL developer to aide you with this issue.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis


Thanks Hugo and Erland (again).

Erland - thank you for your suggestions. The revising of the SET
statement
to a SELECT statement is an obvious time-saver. I don't want you to
think
I haven't tried to test my code - I was trying but every time I tried
to run it to completion I was getting errors that prevented a clean
compile. I have since
isolated some of my problems and redesigned my procedure with more
success.
Per Hugo's suggestion, I have cut a lot of the BS out of the code and
left a single join block as the core of the procedure - much more
logical (to me) and easier on the eyes.

The below procedure seems to work except for one major error and a
minor one:

major: when I simply leave the edit_date column as is, I get the
error:

"ambiguous column name edit_date"

and, when I include the alias with the edit_date column, I get the
following:

"The multi-part identifier "tab3.edit_date" could not be bound."

The lesser problem is that when I added SELECT DISTINCT at the bottom
of
the procedure to avoid duplicates it seems to have thrown off the
paging. I have
designated 10 rows as a default page parameter. This worked well
before I
changed the bottom select statement to eliminate duplicates.

Hugo - I'll try to digest your idea behind changing the join block to
inner joins later tonight. By this, do you mean replacing all "LEFT
JOIN" statements with
"INNER JOIN"?

Otherwise, I hope the updated procedure makes more logical sense.

Thanks again for your tremendous help.
Peter

ALTER PROCEDURE [dbo].[tre_SavedSearch]
@searchname VARCHAR(50) = null, -- The Name Of The User-Defined
Search
@emailaddy VARCHAR(50) = null, -- The ID (email) of the User
@PageNum INT = 1, -- The Starting Page
@PageSize INT = 10, -- The Number of Rows Per Page
@debug INT = 0 -- Debug Value

AS
BEGIN

SET NOCOUNT ON

DECLARE
@saveddate VARCHAR(50),
@savedname VARCHAR(50),
@defaultsearch VARCHAR(50),
@gender VARCHAR(50),
@fromage VARCHAR(50),
@toage VARCHAR(50),
@country VARCHAR(50),
@miles VARCHAR(50),
@pictures VARCHAR(50),
@zipcode VARCHAR(50),
@whereSQL VARCHAR(1000),
@sortID INT -- 1 = registration
-- 2 = recent changes
-- 3 = recent login
-- 4 = distance order

SELECT @saveddate = saved_date, @savedname = saved_name, @gender =
saved_sex,
@fromage = saved_fage, @toage = saved_tage, @country =
saved_country , @miles = saved_miles,
@pictures = saved_pictures, @zipcode = saved_postal, @sortID =
saved_sort
FROM SavedSearches
WHERE saved_name=@searchname
AND email_address=@emailaddy

WITH SavedSearch AS
(

SELECT ROW_NUMBER() OVER ( ORDER BY CASE @sortID
WHEN 1 THEN registerDate
--WHEN 2 THEN tab3.edit_date
WHEN 3 THEN login_date
--WHEN 4 THEN up_order
END DESC
) AS RowNum
,tab1.registerDate
,tab3.edit_date
,tab4.login_date
,tab1.bday_day
,tab1.bday_month
,tab1.bday_year
,tab1.gender
,tab1.zipCode
,tab1.siteId
,tab1.userID
--,tab5.up_order
FROM
(select distinct emailAddress
from Users with(nolock) union select distinct user_name
from PersonalPhotos 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 LastLogin with(nolock)) drv
Left Join Users tab1 on (drv.emailAddress = tab1.emailAddress)
--Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
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)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)

WHERE
(tab1.gender = @gender OR CASE WHEN COALESCE(@gender,'Show All') =
'Show All' THEN 1 ELSE 0 END = 1)
AND
(tab1.country = @country OR CASE WHEN COALESCE(@country,'Show All') =
'Show All' THEN 1 ELSE 0 END = 1)
AND
(tab1.bday_year BETWEEN @toage AND @fromage)
--AND tab2.photo_default = 1 + @photo
)

SELECT registerDate
--,tab3.edit_date
,login_date
,bday_day
,bday_month
,bday_year
,gender
--,up_order
,zipCode
,siteId
,userID
FROM (SELECT DISTINCT registerDate
--,tab3.edit_date
,login_date
,bday_day
,bday_month
,bday_year
,gender
--,up_order
,zipCode
,siteId
,userID
FROM SavedSearch
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize) V
ORDER BY CASE @sortID
WHEN 1 THEN registerDate
--WHEN 2 THEN tab3.edit_date
WHEN 3 THEN login_date
--WHEN 4 THEN up_order
END DESC;
END

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

pbd22 (dushkin@gmail.com) writes:
> The below procedure seems to work except for one major error and a
> minor one:
>
> major: when I simply leave the edit_date column as is, I get the
> error:
>
> "ambiguous column name edit_date"
>
> and, when I include the alias with the edit_date column, I get the
> following:
>
> "The multi-part identifier "tab3.edit_date" could not be bound."

The problem is that when inlucde the alias you do it all over town.
Don't do that. With in the CTE you should do it:

SELECT ROW_NUMBER() OVER (ORDER BY CASE @sortID
WHEN 1 THEN registerDate
--WHEN 2 THEN tab3.edit_date
WHEN 3 THEN login_date
--WHEN 4 THEN up_order
END DESC) AS RowNum,
tab1.registerDate, tab3.edit_date ,tab4.login_date,

And a more general comment, as soon as more than one table is included
in the query, prefix all your columns with aliases (or the table
name). That makes the query easier to follow for an outsider, and
also saves you from accidents if you would add, say, an up_order
column to some other table later on.

However, in the query where you use the CTE:

(SELECT DISTINCT registerDate
--,tab3.edit_date
,login_date
,bday_day
,bday_month
,bday_year
,gender
--,up_order
,zipCode
,siteId
,userID
FROM SavedSearch

You cannot use tab3, because it is not visible at this point. It's
private to the CTE. And since this is a one-table query, there is no
need to use aliases, although it would not be wrong to do so. But then
it would be like:

(SELECT DISTINCT ss.registerDate
,ss.edit_date
,ss.login_date
...
FROM SavedSearch ss


I noticed another issue:

(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 SavedSearches
union
select distinct email_address from UserPrecedence
union
select distinct email_address from LastLogin ) d

First a minor point: As you see I have removed the locking hints. I only
did so, to get less noise. But I recommend that you leave out all hints,
until you have your query working. That helps you to focus on the
essentials.

Then a little bigger point: you can remove the DISTINCT, as UNION
implies DISTINCT. (Use UNION ALL to retain duplicates.)

But the major point is that this just feels wrong. I can't really
say what it is right, because I don't know your tables. But it smells
like an error in the database design. All I can say is that you should
not have to do that.

> The lesser problem is that when I added SELECT DISTINCT at the bottom
> of the procedure to avoid duplicates it seems to have thrown off the
> paging. I have designated 10 rows as a default page parameter.
> This worked well before I changed the bottom select statement to
> eliminate duplicates.

In my experience an urge to add DISTINCT is a token of that the
query is not written in the best way, or that the data model is
problematic. As an explanation of the first, maybe there is a JOIN
that should have been a WHERE EXISTS instead.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

On Jul 20, 2:17 am, Erland Sommarskog wrote:
> pbd22 (dush...@gmail.com) writes:
> > The below procedure seems to work except for one major error and a
> > minor one:
>
> > major: when I simply leave the edit_date column as is, I get the
> > error:
>
> > "ambiguous column name edit_date"
>
> > and, when I include the alias with the edit_date column, I get the
> > following:
>
> > "The multi-part identifier "tab3.edit_date" could not be bound."
>
> The problem is that when inlucde the alias you do it all over town.
> Don't do that. With in the CTE you should do it:
>
> SELECT ROW_NUMBER() OVER (ORDER BY CASE @sortID
> WHEN 1 THEN registerDate
> --WHEN 2 THEN tab3.edit_date
> WHEN 3 THEN login_date
> --WHEN 4 THEN up_order
> END DESC) AS RowNum,
> tab1.registerDate, tab3.edit_date ,tab4.login_date,
>
> And a more general comment, as soon as more than one table is included
> in the query, prefix all your columns with aliases (or the table
> name). That makes the query easier to follow for an outsider, and
> also saves you from accidents if you would add, say, an up_order
> column to some other table later on.
>
> However, in the query where you use the CTE:
>
> (SELECT DISTINCT registerDate
> --,tab3.edit_date
> ,login_date
> ,bday_day
> ,bday_month
> ,bday_year
> ,gender
> --,up_order
> ,zipCode
> ,siteId
> ,userID
> FROM SavedSearch
>
> You cannot use tab3, because it is not visible at this point. It's
> private to the CTE. And since this is a one-table query, there is no
> need to use aliases, although it would not be wrong to do so. But then
> it would be like:
>
> (SELECT DISTINCT ss.registerDate
> ,ss.edit_date
> ,ss.login_date
> ...
> FROM SavedSearch ss
>
> I noticed another issue:
>
> (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 SavedSearches
> union
> select distinct email_address from UserPrecedence
> union
> select distinct email_address from LastLogin ) d
>
> First a minor point: As you see I have removed the locking hints. I only
> did so, to get less noise. But I recommend that you leave out all hints,
> until you have your query working. That helps you to focus on the
> essentials.
>
> Then a little bigger point: you can remove the DISTINCT, as UNION
> implies DISTINCT. (Use UNION ALL to retain duplicates.)
>
> But the major point is that this just feels wrong. I can't really
> say what it is right, because I don't know your tables. But it smells
> like an error in the database design. All I can say is that you should
> not have to do that.
>
> > The lesser problem is that when I added SELECT DISTINCT at the bottom
> > of the procedure to avoid duplicates it seems to have thrown off the
> > paging. I have designated 10 rows as a default page parameter.
> > This worked well before I changed the bottom select statement to
> > eliminate duplicates.
>
> In my experience an urge to add DISTINCT is a token of that the
> query is not written in the best way, or that the data model is
> problematic. As an explanation of the first, maybe there is a JOIN
> that should have been a WHERE EXISTS instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Hi Erland,

Thanks again. Your advice did the job with the alias problem.
It turns out the SELECT DISTINCT issue was a bigger problem.
I am wondering if we are talking about the same "SELECT DISTINCT"?
I wasn't referring to the DISTINCT naming in the JOIN/UNION block, but
the SELECT DISTINCT at the bottom of the stored procedure:

FROM (SELECT DISTINCT registerDate
--,tab3.edit_date
,login_date
[snip]
,userID
FROM SavedSearch
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize) V
ORDER BY CASE @sortID ... [snip]

without the above select distinct, there is a pretty bad duplicate
problem in the results. The problem is that the above seems to throw
off paging. When I remove the SELECT DISTINCT from the above, the
paging problem is solved but the duplicate problem is back.

If the above was the SELECT DISTINCT you meant, then sorry for
misreading. I
will continue to try to work out why the duplicates are happening in
the stored procedure logic.

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

pbd22 (dushkin@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

On Jul 20, 2:26 pm, Erland Sommarskog wrote:
> pbd22 (dush...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Hi Erland,

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

Thanks again.

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

On Jul 20, 4:39 pm, pbd22 wrote:
> On Jul 20, 2:26 pm, Erland Sommarskog wrote:
>
> > pbd22 (dush...@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, esq...@sommarskog.se
>
> > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx
>
> Hi Erland,
>
> Thanks. I have sent you an email. I'll check back here for a
> continuation of the thread.
>
> Thanks again.

Hi Erland et al.

I am still at it with a little bit more understanding to go on.
My problem, as I understand it, is that I have (at least) one
one-to-many table relationship involved in my JOIN statement.
So, what is happening here is that the query is returning each
individual instance of an email address in any given table as a
new row. This is how I understand things so far.

To correct the problem, I understand that the appropriate placement
of a GROUP BY statement is one possible solution. This is where
I need a little help.

I have been advised to follow one of two possible templates in the
constructon
of my GROUP BY clause:

The first:

--Only work on SQL Server 2005
Select Distinct Col1,Col2,(Select Email + '; ' as [text()] from #data
sub Where sub.col1=main.col1 and sub.col2=main.col2 For XML Path(''))
>From #data main

And the second:

SELECT
m.Column1,
m.Column2,
dt.Column1,
dt.Column2,
etc
FROM MyTable m
JOIN ( SELECT
Column1,
Column2,
etc
FROM MyManyTable
GROUP BY
Column1,
Column2,
etc
) dt
ON m.PKColumn = dt.FKColumn

I am not sure which one is more efficient/effective, but I feel I have
a better understanding of the second. I have tried GROUP BY after both
WHERE conditions in my stored procedure and keep getting the error:

"The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator."

I get this error once I have been forced to add every column name in
the GROUP BY statement. But, I am trying to just add one...
tab1.emailAddress (from the Users table). Shouldn't I just be grouping
by this column alone?

Assuming that you agree with this solution to my duplicate problem,

OTHER INFORMATION:

In my Multi-table JOIN statement, all tables are combined via the
common Users.emailAddress column. But, the Email Addresses are not
designated as primary/foreign keys. That is reserved for ID columns in
each table, as such:

The Primary Keys For Each Table:

USERS = userID
SAVEDSEARCHES = saved_ID
LASTLOGIN = login_ID
PERSONALPHOTOS = photoId
EDITPROFILE = edit_id

The Foreign Key Relationships For Each Table:

USERPRECEDENCE = userID (to userID in Users Table)

WHAT I AM TRYING TO DO:
This search returns user profiles based on customized search
settings.
This procedure attempts to do 3 distinct things:
1) the top block of code uses passed parameters to find the search
name
and user email in SavedSearches and returns associated data used for
conditional statements (WHERE, ORDER BY).
2) The middle block uses that data as conditions when joining the the
relevant tables. This block uses Row_Number to count rows and
populates
the SavedSearch alias.
3) The final select pulls the table data and paging information from
SavedSearch, returning paging information and column data. I am
guessing this is where my GROUP BY statement should be?

CREATE STATEMENTS:

DBO.USERS

USE [MyDB]
GO
/****** Object: Table [dbo].[Users] Script Date: 07/21/2007
23:05:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
[registerDate] [datetime] NULL,
[password] [varchar](50) NULL,
[role] [varchar](50) NULL,
[securityQuestion] [varchar](50) NULL,
[securityAnswer] [varchar](50) NULL,
[zipCode] [varchar](50) NULL,
[alternateEmail] [varchar](50) NULL,
[emailAddress] [varchar](50) NULL,
[bday_month] [varchar](50) NULL,
[bday_day] [varchar](50) NULL,
[bday_year] [varchar](50) NULL,
[userID] [int] [primary key] IDENTITY(1,1) NOT NULL,
[gender] [varchar](50) NULL,
[siteId] [varchar](50) NULL,
[city] [varchar](50) NULL,
[state] [varchar](50) NULL,
[country] [varchar](50) NULL,
[edit_date] [varchar](50) NULL,
[lastName] [varchar](50) NULL,
[firstName] [varchar](50) NULL,
[confirmed] [bit] NULL DEFAULT ((0)),
CONSTRAINT [PK_userID] PRIMARY KEY CLUSTERED
(
[userID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

DBO.USERS INSERT STATEMENT:

INSERT INTO [MyDB].[dbo].[Users]
([registerDate]
,[password]
,[role]
,[securityQuestion]
,[securityAnswer]
,[zipCode]
,[alternateEmail]
,[emailAddress]
,[bday_month]
,[bday_day]
,[bday_year]
,[gender]
,[siteId]
,[city]
,[state]
,[country]
,[edit_date]
,[lastName]
,[firstName]
,[confirmed])
VALUES
(
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,)

DBO.SAVEDSEARCHES

USE [MyDB]
GO
/****** Object: Table [dbo].[SavedSearches] Script Date:
07/21/2007 23:10:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SavedSearches](
[saved_id] [int] IDENTITY(1,1) NOT NULL,
[saved_query] [text] NULL,
[email_address] [varchar](50) NULL,
[saved_date] [datetime] NULL,
[saved_name] [varchar](50) NULL,
[is_default] [bit] NULL,
[saved_sex] [varchar](50) NULL,
[saved_fage] [varchar](50) NULL,
[saved_tage] [varchar](50) NULL,
[saved_country] [varchar](50) NULL,
[saved_miles] [varchar](50) NULL,
[saved_pictures] [varchar](50) NULL,
[saved_postal] [varchar](50) NULL,
[saved_sort] [varchar](50) NULL,
[saved_photo_string] [varchar](50) NULL,
[saved_orderby] [int] NULL,
CONSTRAINT [PK__SavedSearches__690797E6] PRIMARY KEY CLUSTERED
(
[saved_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

DBO.PERSONALPHOTOS

USE [MyDB]
GO
/****** Object: Table [dbo].[PersonalPhotos] Script Date:
07/21/2007 23:14:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PersonalPhotos](
[photoId] [int] IDENTITY(1,1) NOT NULL,
[photo_name] [varchar](50) NULL,
[photo_location] [varchar](100) NULL,
[photo_size] [varchar](50) NULL,
[user_name] [varchar](50) NULL,
[photo_caption] [varchar](50) NULL,
[photo_default] [bit] NULL,
[photo_private] [bit] NULL,
[photo_date] [datetime] NULL,
[no_photo] [bit] NULL,
CONSTRAINT [PK_PersonalPhotos] PRIMARY KEY CLUSTERED
(
[photoId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

DBO.PERSONALPHOTOS INSERT STATEMENT:

DBO.LASTLOGIN:

USE [MyDB]
GO
/****** Object: Table [dbo].[LastLogin] Script Date: 07/21/2007
23:18:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LastLogin](
[login_id] [int] IDENTITY(1,1) NOT NULL,
[login_date] [datetime] NULL,
[email_address] [varchar](50) NULL,
[login_status] [bit] NOT NULL CONSTRAINT [DF_lastlogin_login_status]
DEFAULT (0),
[login_activity] [datetime] NOT NULL CONSTRAINT
[DF_lastlogin_login_activity] DEFAULT (getutcdate()),
[login_isonline] AS (case when ([login_status] = 1 and
(datediff(minute,[login_activity],getutcdate()) < 30)) then 1 else 0
end),
CONSTRAINT [PK__LastLogin__5F7E2DAC] PRIMARY KEY CLUSTERED
(
[login_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

DBO.EDITPROFILE:

USE [MyDB]
GO
/****** Object: Table [dbo].[EditProfile] Script Date: 07/21/2007
23:20:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EditProfile](
[edit_id] [int] IDENTITY(1,1) NOT NULL,
[headline] [varchar](50) NULL,
[about_me] [text] NULL,
[edit_date] [datetime] NULL,
[email_address] [varchar](50) NULL,
[public_name] [varchar](50) NULL,
[interests] [text] NULL,
CONSTRAINT [PK__EditProfile__58D1301D] PRIMARY KEY CLUSTERED
(
[edit_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

pbd22 (dushkin@gmail.com) writes:
> I have been advised to follow one of two possible templates in the
> constructon
> of my GROUP BY clause:

It was not my advices, I hope!

> I get this error once I have been forced to add every column name in
> the GROUP BY statement. But, I am trying to just add one...
> tab1.emailAddress (from the Users table). Shouldn't I just be grouping
> by this column alone?
>
> Assuming that you agree with this solution to my duplicate problem,

No, I don't. Looking at your tables it's even more clear what I suspected:
you need to redesign your tables.

All your tables have a IDENTITY column as the primary key. There are
definitely cases where an artificial key makes sense. Either because
the natural key is unpractical, or a real natural key cannot be
identified. But when you have artificial keys in all tables, and then
try to use a non-key value as a key, that is when you are in trouble.

Am I to guess that to use your system, the user logs in with his e-mail?
In such case, why in this table:

> CREATE TABLE [dbo].[Users](
> [registerDate] [datetime] NULL,
> [password] [varchar](50) NULL,
> [role] [varchar](50) NULL,
> [securityQuestion] [varchar](50) NULL,
> [securityAnswer] [varchar](50) NULL,
> [zipCode] [varchar](50) NULL,
> [alternateEmail] [varchar](50) NULL,
> [emailAddress] [varchar](50) NULL,
> [bday_month] [varchar](50) NULL,
> [bday_day] [varchar](50) NULL,
> [bday_year] [varchar](50) NULL,
> [userID] [int] [primary key] IDENTITY(1,1) NOT NULL,

Is the email address nullable. In fact, all columns are nullable. A user
could be just an IDENTITY value and a bunch of NULL values. How useful
is that?

Please answer these question:
1) Can there be a user for which there is no email address registered?
2) Can there be two users with the same email address?

Depending on the business rules, the answer can very well be yes on
both questions, but in such case your efforts with the stored procedure
appear futile. So my assumption is that the answer is no both questions,
and that emailAddress should be the primary key of this table.
Alternatively, there should be a UNIQUE constraints.

So what should you use in the other tables? Well, this is a case where
it makes sense to use an artificial PK as a surrogate. If a user changes
his email address - which appears to be a reasonable operation to permit -
you will only need to update the email address in once place. Where foreign
keys can be set up to be cascading, it's far easier to use UserID in
other tables.

Next table:

> CREATE TABLE [dbo].[SavedSearches](
> [saved_id] [int] IDENTITY(1,1) NOT NULL,
> [saved_query] [text] NULL,
> [email_address] [varchar](50) NULL,

Again, all columns are nullable but the IDENTITY column. What about
the email address here? What point does it make to have it NULL?
And more importantly, can there be an email address here that does not
exist in Users?

I strongly suspect that you should replace email_address with a
UserID and an FK to the users table.

As for the saved_id, I don't think table makes much sense. You would
be better of having a PK which is (UserID, searchno), where searchno
is a running number for the user. Or simply (UserId, saved_name). (I
assume that users are permitted to save more than one search.)

> CREATE TABLE [dbo].[PersonalPhotos](
> [photoId] [int] IDENTITY(1,1) NOT NULL,
> [photo_name] [varchar](50) NULL,
> [photo_location] [varchar](100) NULL,
> [photo_size] [varchar](50) NULL,
> [user_name] [varchar](50) NULL,
> [photo_caption] [varchar](50) NULL,
> [photo_default] [bit] NULL,
> [photo_private] [bit] NULL,
> [photo_date] [datetime] NULL,
> [no_photo] [bit] NULL,
> CONSTRAINT [PK_PersonalPhotos] PRIMARY KEY CLUSTERED
> (
> [photoId] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
>= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]

There is no email address in this table, but there is a user_name? Am I
to guess that is another name for the email address? Again, replace it
with UserID and a FK to Users.

The PK of this table should probably be (UserID, photo_name).

> CREATE TABLE [dbo].[LastLogin](
> [login_id] [int] IDENTITY(1,1) NOT NULL,
> [login_date] [datetime] NULL,
> [email_address] [varchar](50) NULL,

Another email_address. Can an email address log in without being in
Users? Again, put in the UserID here.

As for the PK, this is a little more tricky. Theoretically, (UserID,
login_date) is the right pick. But time values are not really good for
PKs, since time is a continuous entity, and all values in a computer
are discreet. Then again, the likelyhood that a person will login within
the same 3.33 ms, the resolution of datetime, is likely to be extremely
small, so for this table it works.

> CREATE TABLE [dbo].[EditProfile](
> [edit_id] [int] IDENTITY(1,1) NOT NULL,
> [headline] [varchar](50) NULL,
> [about_me] [text] NULL,
> [edit_date] [datetime] NULL,
> [email_address] [varchar](50) NULL,
> [public_name] [varchar](50) NULL,
> [interests] [text] NULL,

Again, can there be a row here, but not one in Users? Assuming that
edit_date is date and time, (UserID, edit_date) can probably serve as PK.

I predict that once you have made these changes, you will find your
procedure a lot easier to write. That does not mean that I how it should
look like. After all, I still don't see the full picture of what you are
trying to do.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

Thanks a ton Erland.

In response to your questions:

> Please answer these question:
> 1) Can there be a user for which there is no email address registered?
ANSWER: NO
> 2) Can there be two users with the same email address?
ANSWER: NO

> So my assumption is that the answer is no both questions,
> and that emailAddress should be the primary key of this table... [SNIP]

OK. I have summarized the changes that you have suggested below, let
me know if you agree with them:

Users Table

a) change primary key from userID to emailAddress
b) leave userID as unique and not null

Last Login

a) create userID column, set it as not null and unique
b) remove primary key from login_id column (should I delete this
column?)
c) make login_date the primary key column
d) set foreign key from userID in Last Login (souce) to userID in
Users (destination)
e) delete the email_address column

PersonalPhotos

a) create userID column, set it as not null and unique
b) remove primary key from photoId column (should I delete this
column?)
c) make photo_name the primary key column
d) set foreign key from userID in PersonalPhotos (souce) to userID in
Users (destination)
e) delete the user_name column


SavedSearches

a) create userID column, set it as not null and unique
b) remove primary key from search_id column (should I delete this
column?)
c) make search_name the primary key column
d) set foreign key from userID in SavedSearches (souce) to userID in
Users (destination)
e) delete the email_address column

EditProfile

a) create userID column, set it as not null and unique
b) remove primary key from edit_id column (should I delete this
column?)
c) make edit_date the primary key column
d) set foreign key from userID in EditProfile (souce) to userID in
Users (destination)
e) delete the email_address column


UserPrecedence

I figure I should bring up UserPrecedence at this point.
UserPrecedence is a table that is used to describe the results of a
ZipCode search. The order of the results is
detailed in the UserPrecedence table. I had this hard-coded before but
realized that if a user deletes himself from the system, that will
cause errors in the saved results. By creating the UserPrecedence
table, I am able to delete records here also when a user removes
himself.

So, the way I originally had it was:

email_address - is the user conducting the search
userID - is the userID of the user returned in the search results
up_order - is the ZipCode order (by distance) in the results
search_name - the name of the saved search

But, since we have been using a surrogate key, removing email_address
columns and replacing it with userID to denote the current user, I am
wondering if it is possible to have two userID columns (one describing
the user that conducted the search and the other the users in the
results)? I am guessing not. How would you handle this?

Finally, here is a sketch of the updated Data Model per your suggested
changes (its in UML form; asterisks to the left mean "not null"):

http://i103.photobucket.com/albums/m156/pbd22/SavedSearch_Da taModel.jpg

Thanks again for your help.
Peter

PS - I thought I had explained what I am trying to do with this
procedure. But, maybe you are looking for a different response? This
procedure returns the results of user-customized searches. The top
part calls saved search terms from the SavedSearches table and stores
them (gender, country, etc) as local parameters.
Those parameters are then used to sort against the relevant JOINed
tables, encapsulating the results in the alias SavedSearch. The bottom
third of the stored procedure searches against the results in
SavedSearch, using the Row_Number function to return paging
information with the final table. The final result is a series of
profiles per the user's original saved search terms.

If I still am not providing what you want to hear, maybe you could
elaborate a little on what you expect in my explanation?

Re: Establishing Precedence In ORDERBY Condition Causing Problems.

pbd22 (dushkin@gmail.com) writes:
> Users Table
>
> a) change primary key from userID to emailAddress
> b) leave userID as unique and not null

Or put a UNIQUE constraint on emailAddress. It does not matter that much,
but since FKs will be to UserID I prefer that to be the PK.

> Last Login
>
> a) create userID column, set it as not null and unique
> b) remove primary key from login_id column (should I delete this
> column?)
> c) make login_date the primary key column
> d) set foreign key from userID in Last Login (souce) to userID in
> Users (destination)
> e) delete the email_address column

I realise that the table is called LastLogin. Does this mean that there
is only one row per user? In such case I would rather have the columns
in Users.

But the normal would be to maintain a history, and have all the logins
a user has performed. In such case you cannot make UserID unique, because
he is only able to log in once. And login_date cannot be the PK, because
then two users can't login the same day.

The primary key should be composite (UserID, login_date). By the way,
despite the name, I assume that login_date also contains a time portion?
Else this will not fly well, as a user would only be able to login once
per day.

To the other questions, yes drop login_id and email_address. And UserID
should be an FK to Users.

> PersonalPhotos
>
> a) create userID column, set it as not null and unique
> b) remove primary key from photoId column (should I delete this
> column?)
> c) make photo_name the primary key column
> d) set foreign key from userID in PersonalPhotos (souce) to userID in
> Users (destination)
> e) delete the user_name column

Again, you need a composite primary key (UserID, photo_name) For b, d
and e the same applies as to LastLogin.

> SavedSearches
>
> a) create userID column, set it as not null and unique
> b) remove primary key from search_id column (should I delete this
> column?)
> c) make search_name the primary key column
> d) set foreign key from userID in SavedSearches (souce) to userID in
> Users (destination)
> e) delete the email_address column

Same here: you need a composite primary key. Again, b, d and e apply.

> EditProfile
>
> a) create userID column, set it as not null and unique
> b) remove primary key from edit_id column (should I delete this
> column?)
> c) make edit_date the primary key column
> d) set foreign key from userID in EditProfile (souce) to userID in
> Users (destination)
> e) delete the email_address column

Again, you need a composite primary key. Again, b, d and e apply.

> So, the way I originally had it was:
>
> email_address - is the user conducting the search
> userID - is the userID of the user returned in the search results
> up_order - is the ZipCode order (by distance) in the results
> search_name - the name of the saved search
>
> But, since we have been using a surrogate key, removing email_address
> columns and replacing it with userID to denote the current user, I am
> wondering if it is possible to have two userID columns (one describing
> the user that conducted the search and the other the users in the
> results)? I am guessing not. How would you handle this?

You cannot of course have two columns with the same name, but it's
perfectly normal to have two columns that are foreign keys to the
same table. You will have to find good names for your user IDs, so
that you easily can tell them apart. Maybe searching_user_id and
returned_user_id.

> If I still am not providing what you want to hear, maybe you could
> elaborate a little on what you expect in my explanation?

The standard recommendation when people ask for help with queries is
that they provide:

o CREATE TABLE statement(s) for their table(s). Preferably simplified
to illustrate the problem at hand.
o INSERT statement(s) with sample data.
o The desired result given the sample.
o A short description what you are trying to achieve.

This helps a lot to clarify what you are trying to. Also, having
table and data as INSERT statements makes it simple to develop a
tested solution.

When you posted the old definition of the tables, you included some
INSERT templates from Mgmt Studio, but that does not cut it. It has
to be real test data. If you hope that me or anyone else is going to
fill in that for you, you're taking a gamble. And after all, you know
the business, so you should also know what is good test data.

But maybe you will be able to write the procedure on your own, once
you have cleaned up the data model.


In any case, I'm off for a trip, so I will be away for ten days or so.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx