Posted by Erland Sommarskog on 07/25/07 22:22

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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]