JOIN Question

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

LVL 20
REA_ANDREWAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
dqmqConnect With a Mentor Commented:
>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
 
MikeTooleCommented:
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
 
REA_ANDREWAuthor Commented:
I have tried both and your solution worked.  Thankyou
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.