July 21, 2006 at 8:46 am
Is there a way to include the DISTINCT in a select with the inner join without writing out each column in the select list that i need returned?
Erik
DECLARE
@pUserID uniqueidentifier
SELECT
@pUserID = UserID FROM aspnet_users where Username = 'someusername'
SELECT
aspnet_Users.*, users.*
FROM
aspnet_Users INNER JOIN
Users
ON aspnet_Users.UserId = Users.UserID
where
aspnet_users.userid = @pUserID
Dam again!
July 21, 2006 at 9:27 am
If you can keep from using DISTINCT, that is always best, (generally that indicates a problem with relationships of lack of constraints).
You may want to post your table structures, some dummy data, and the output you are getting and the output you want.
I wasn't born stupid - I had to study.
July 21, 2006 at 9:39 am
A little more info (e.g. DDL) would be helpful but I'll take a stab...
If the final result set is supposed to be DISTINCT'd then SELECT DISTINCT followed by the rest of the query should work.
If the aspnet_users is needed DISTINCT'd try:
SELECT
dt.*, users.* FROM (SELECT DISTINCT * FROM aspnet_Users) dt INNER JOIN Users ON dt.UserId = Users.UserID
If users is the one try:
SELECT
aspnet_Users.*, dt.*
FROM aspnet_Users
INNER JOIN (SELECT DISTINCT * FROM Users) dt ON aspnet_Users.UserId = dt.UserID
But if I was a betting man, I would guess that UserId is a PK in the user table. Further guessing... UserID and another unique value (e.g. applicationID) is the PK in aspnet_users, thus, the result set from your original query is, in reality, DISTINCT.
Close at all?
July 21, 2006 at 10:22 am
I have a one to one relationship with a tables called aspnet_users and Users
The aspnet_users is new to the .net 2005 that aids in memberships mamagement so i my app the first thing a user has to do is get a username.
so i am just extending the aspnet_users.
SELECT
aspnet_Users.*, users.*
FROM aspnet_Users
INNER JOIN
Users
ON aspnet_Users.UserId = Users.UserID
where aspnet_Users.Username = 'someusername'
====================================================
so when i do my querry i would like to return the usersID only once. Is that possible with the way that i have my querry structured or would i need to explict every column name?
Thanks for the replys!
Erik
USE
[AFCCxxxx]
GO
/****** Object: Table [dbo].[aspnet_Users] Script Date: 07/21/2006 09:15:10 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TABLE [dbo].[aspnet_Users](
[ApplicationId] [uniqueidentifier]
NOT NULL,
[UserId] [uniqueidentifier]
NOT NULL DEFAULT (newid()),
[UserName] [nvarchar]
(256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LoweredUserName] [nvarchar]
(256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MobileAlias] [nvarchar]
(16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT (NULL),
[IsAnonymous] [bit]
NOT NULL DEFAULT ((0)),
[LastActivityDate] [datetime]
NOT NULL,
PRIMARY
KEY NONCLUSTERED
(
[UserId]
ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]
GO
ALTER
TABLE [dbo].[aspnet_Users] WITH CHECK ADD CONSTRAINT [FK__aspnet_Us__Appli__0425A276] FOREIGN KEY([ApplicationId])
REFERENCES
[dbo].[aspnet_Applications] ([ApplicationId])
GO
ALTER
TABLE [dbo].[aspnet_Users] CHECK CONSTRAINT [FK__aspnet_Us__Appli__0425A276]
USE
[AFCCxxxx]
GO
/****** Object: Table [dbo].[Users] Script Date: 07/21/2006 09:15:30 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE
TABLE [dbo].[Users](
[UserID] [uniqueidentifier]
NOT NULL,
[Prefix] [nvarchar]
(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirstName] [nvarchar]
(30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Middle] [nvarchar]
(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [nvarchar]
(30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Suffix] [nvarchar]
(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SecondaryEmail] [nvarchar]
(200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Website] [nvarchar]
(200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Note] [text]
COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserTypeCD] [char]
(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsLockedOut] [bit]
NOT NULL CONSTRAINT [DF_Users_IsLockedOut] DEFAULT ((0)),
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID]
ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
GO
ALTER
TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [FK_Users_aspnet_Users] FOREIGN KEY([UserID])
REFERENCES
[dbo].[aspnet_Users] ([UserId])
ON
UPDATE CASCADE
ON
DELETE CASCADE
GO
ALTER
TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_aspnet_Users]
GO
ALTER
TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [FK_Users_Users.UserType] FOREIGN KEY([UserTypeCD])
REFERENCES
[dbo].[Users.UserType] ([UserTypeCD])
GO
ALTER
TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Users.UserType]
Dam again!
July 21, 2006 at 10:44 am
The list of columns which the DISTINCT keyword will operate on is already provided in the SELECT list. Yu just need to add the DISTINCT keyword. If you want to ensure distinct values for only a subset of the columns you are currenetly returning, you would need to do something like using a GROUP BY clause, and you will need to decide what you awant to do with all the extra values that occur in your 'duplicate' records; i.e. how to aggregate them.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 21, 2006 at 3:31 pm
Do you have an example?
thanks
erik
Dam again!
July 21, 2006 at 3:57 pm
Have you run this query? You should not get multiple values for a one-to-one relationship, hence DISITINCT should not be necessary.
I wasn't born stupid - I had to study.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply