Select one single row per entry in users table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select one single row per entry in users table

Author

Topic

tiwas
Starting Member

37 Posts

Posted - 2010-02-02 : 06:39:25

Hi,

I'm trying to get this query to work a little better, but I'm kind of stuck:
SELECT aspnet_Users.UserId, aspnet_Users.UserName, aspnet_Users.LastActivityDate, aspnet_Roles.RoleName, aspnet_Roles.Description, aspnet_Membership.Email,
aspnet_Membership.IsApproved, aspnet_Membership.IsLockedOut, aspnet_Membership.CreateDate, aspnet_Membership.LastPasswordChangedDate,
aspnet_Membership.FailedPasswordAttemptCount, aspnet_Membership.Comment
FROM aspnet_Membership RIGHT OUTER JOIN
aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId LEFT OUTER JOIN
aspnet_Roles INNER JOIN
aspnet_UsersInRoles ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId

What I'm trying to do here is to get just one row returned per user, and it's currently returning several for some users as they may have more than one role (aspnet_UsersInRoles). What I would like is to have a pulldown menu or something with the different roles without wasting rows in my table.

Anyone able to help me figure this out?

Cheers!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 06:44:41

are you using sql 2005? also which role you want to return with usera who've multiple roles ? does that matter?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-02 : 07:25:24

Make use of this
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

raky
Aged Yak Warrior

767 Posts

Posted - 2010-02-02 : 07:29:05

I think he wants the Roles of a user as comma separated then he may get one record per one user. For that atleast he need to post the tables structure.

tiwas
Starting Member

37 Posts

Posted - 2010-02-02 : 08:33:06

How the roles are output is really not important, as I will get the list of roles for each user when asp.net populates each row.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 09:26:10

quote:
Originally posted by tiwas

How the roles are output is really not important, as I will get the list of roles for each user when asp.net populates each row.

you still didnt answer my first question.

tiwas
Starting Member

37 Posts

Posted - 2010-02-10 : 04:06:29

Sorry. I'm using MSSQL 2008, but when I publish I might end up with 2005.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 04:14:49

quote:
Originally posted by tiwas

Sorry. I'm using MSSQL 2008, but when I publish I might end up with 2005.


SELECT aspnet_Users.UserId, aspnet_Users.UserName, aspnet_Users.LastActivityDate,LEFT(t.u,LEN(t.u)-1) , aspnet_Membership.Email, 
aspnet_Membership.IsApproved, aspnet_Membership.IsLockedOut, aspnet_Membership.CreateDate, aspnet_Membership.LastPasswordChangedDate, 
aspnet_Membership.FailedPasswordAttemptCount, aspnet_Membership.Comment
FROM aspnet_Membership RIGHT OUTER JOIN
aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId 
CROSS APPLY(SELECT 'Role: ' + aspnet_Roles.RoleName + 'Description: ' +  aspnet_Roles.Description  + ';'
FROM aspnet_Roles 
INNER JOIN
aspnet_UsersInRoles ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId 
WHERE aspnet_UsersInRoles.UserId=aspnet_Users.UserId 
FOR XML PATH('')) t(u)

------------------------------------------------------------------------------------------------------
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources