Solved

JOIN Question

Posted on 2007-11-19
3
751 Views
Last Modified: 2010-04-21
I have 3 tables

aspnet_Membership (UserID)
aspnet_Roles(RoleID)
aspnet_UsersInRoles(RoleID,UserID)

Now I have pasted the code I am using at current but.  Becuase I have a user assigned to 2 roles that user appears in the reults.  I simply want to show unique users based on whether they are in any of the roles in the where clause.  If they are in 2 or more roles I still only wish them to appear in the results once.
SELECT        aspnet_Membership.ApplicationId, aspnet_Membership.UserId, aspnet_Membership.Password, aspnet_Membership.PasswordFormat, 

                         aspnet_Membership.PasswordSalt, aspnet_Membership.MobilePIN, aspnet_Membership.Email, aspnet_Membership.LoweredEmail, 

                         aspnet_Membership.PasswordQuestion, aspnet_Membership.PasswordAnswer, aspnet_Membership.IsApproved, aspnet_Membership.IsLockedOut, 

                         aspnet_Membership.CreateDate, aspnet_Membership.LastLoginDate, aspnet_Membership.LastPasswordChangedDate, 

                         aspnet_Membership.LastLockoutDate, aspnet_Membership.FailedPasswordAttemptCount, aspnet_Membership.FailedPasswordAttemptWindowStart, 

                         aspnet_Membership.FailedPasswordAnswerAttemptCount, aspnet_Membership.FailedPasswordAnswerAttemptWindowStart, 

                         aspnet_UsersInRoles.UserId AS Expr1, aspnet_Roles.RoleName

FROM            aspnet_Membership INNER JOIN

                         aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId INNER JOIN

                         aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId LEFT OUTER JOIN

                         aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId

WHERE        (aspnet_Roles.RoleName = 'Administrator') OR

                         (aspnet_Roles.RoleName = 'POWERUSER')

Open in new window

0
Comment
Question by:REA_ANDREW
3 Comments
 
LVL 27

Expert Comment

by:MikeToole
ID: 20314501
You can't return the RoleName if you just want the user once.
SELECT        aspnet_Membership.ApplicationId, aspnet_Membership.UserId, aspnet_Membership.Password, aspnet_Membership.PasswordFormat, 

                         aspnet_Membership.PasswordSalt, aspnet_Membership.MobilePIN, aspnet_Membership.Email, aspnet_Membership.LoweredEmail, 

                         aspnet_Membership.PasswordQuestion, aspnet_Membership.PasswordAnswer, aspnet_Membership.IsApproved, aspnet_Membership.IsLockedOut, 

                         aspnet_Membership.CreateDate, aspnet_Membership.LastLoginDate, aspnet_Membership.LastPasswordChangedDate, 

                         aspnet_Membership.LastLockoutDate, aspnet_Membership.FailedPasswordAttemptCount, aspnet_Membership.FailedPasswordAttemptWindowStart, 

                         aspnet_Membership.FailedPasswordAnswerAttemptCount, aspnet_Membership.FailedPasswordAnswerAttemptWindowStart, 

--                         aspnet_UsersInRoles.UserId AS Expr1, aspnet_Roles.RoleName

FROM            aspnet_Membership 

WHERE  UserID IN ( 

                  Select Distinct UserID 

                  From aspnet_UsersInRoles Inner JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId

                  WHERE (aspnet_Roles.RoleName = 'Administrator') OR (aspnet_Roles.RoleName = 'POWERUSER')

                  );

Open in new window

0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 20314614
>If they are in 2 or more roles I still only wish them to appear in the results once.

But then you can show at most one RoleName per userid in the output, right? I took the liberty to remove the outer join since at least one role (from the where clause) must be present.

SELECT 

 aspnet_Membership.ApplicationId,

 aspnet_Membership.UserId,

 aspnet_Membership.Password,

 aspnet_Membership.PasswordFormat, 

 aspnet_Membership.PasswordSalt,

 aspnet_Membership.MobilePIN,

 aspnet_Membership.Email,

 aspnet_Membership.LoweredEmail, 

 aspnet_Membership.PasswordQuestion,

 aspnet_Membership.PasswordAnswer,

 aspnet_Membership.IsApproved,

 aspnet_Membership.IsLockedOut, 

 aspnet_Membership.CreateDate,

 aspnet_Membership.LastLoginDate,

 aspnet_Membership.LastPasswordChangedDate,

 aspnet_Membership.LastLockoutDate,

 aspnet_Membership.FailedPasswordAttemptCount,

 aspnet_Membership.FailedPasswordAttemptWindowStart,

 aspnet_Membership.FailedPasswordAnswerAttemptCount,

 aspnet_Membership.FailedPasswordAnswerAttemptWindowStart,

 count(aspnet_Roles.RoleName) CountOfRoles

FROM aspnet_Membership

INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId INNER JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId

INNER JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId

WHERE aspnet_Roles.RoleName in ('Administrator','POWERUSER')

GROUP BY

 aspnet_Membership.ApplicationId,

 aspnet_Membership.UserId,

 aspnet_Membership.Password,

 aspnet_Membership.PasswordFormat, 

 aspnet_Membership.PasswordSalt,

 aspnet_Membership.MobilePIN,

 aspnet_Membership.Email,

 aspnet_Membership.LoweredEmail, 

 aspnet_Membership.PasswordQuestion,

 aspnet_Membership.PasswordAnswer,

 aspnet_Membership.IsApproved,

 aspnet_Membership.IsLockedOut, 

 aspnet_Membership.CreateDate,

 aspnet_Membership.LastLoginDate,

 aspnet_Membership.LastPasswordChangedDate,

 aspnet_Membership.LastLockoutDate,

 aspnet_Membership.FailedPasswordAttemptCount,

 aspnet_Membership.FailedPasswordAttemptWindowStart,

 aspnet_Membership.FailedPasswordAnswerAttemptCount,

 aspnet_Membership.FailedPasswordAnswerAttemptWindowStart

Open in new window

0
 
LVL 20

Author Closing Comment

by:REA_ANDREW
ID: 31409996
I have tried both and your solution worked.  Thankyou
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now