|
|
Posted by pbd22 on 07/25/07 17:23
On Jul 20, 4:39 pm, pbd22 <dush...@gmail.com> wrote:
> On Jul 20, 2:26 pm, Erland Sommarskog <esq...@sommarskog.se> 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/downloads/books...
> > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> Hi Erland,
>
> Thanks. I have sent you an email. I'll check back here for a
> continuation of the thread.
>
> Thanks again.
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
(<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
Navigation:
[Reply to this message]
|