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

(snip)
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

On Jul 18, 3:06 pm, Hugo Kornelis
<h... (AT) perFact (DOT) REMOVETHIS.info.INVALID> wrote:

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

pbd22 (dushkin (AT) gmail (DOT) com) writes:
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.

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 (AT) sommarskog (DOT) 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

On Jul 20, 2:17 am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
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.

pbd22 (dushkin (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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

On Jul 20, 2:26 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Hi Erland,

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

Thanks again.

On Jul 20, 4:39 pm, pbd22 <dush... (AT) gmail (DOT) com> wrote:
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(''))
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
(<registerDate, datetime,>
,<password, varchar(50),>
,<role, varchar(50),>
,<securityQuestion, varchar(50),>
,<securityAnswer, varchar(50),>
,<zipCode, varchar(50),>
,<alternateEmail, varchar(50),>
,<emailAddress, varchar(50),>
,<bday_month, varchar(50),>
,<bday_day, varchar(50),>
,<bday_year, varchar(50),>
,<gender, varchar(50),>
,<siteId, varchar(50),>
,<city, varchar(50),>
,<state, varchar(50),>
,<country, varchar(50),>
,<edit_date, varchar(50),>
,<lastName, varchar(50),>
,<firstName, varchar(50),>
,<confirmed, bit,>)

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

pbd22 (dushkin (AT) gmail (DOT) com) writes:
It was not my advices, I hope!

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:

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:

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

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

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.

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 (AT) sommarskog (DOT) se

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

Thanks a ton Erland.

In response to your questions:


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/m..._DataModel.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?

pbd22 (dushkin (AT) gmail (DOT) com) writes:
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.

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.

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

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

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

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.

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 (AT) sommarskog (DOT) 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