Solved

JOIN Question

Posted on 2007-11-19
3
752 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

910 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

25 Experts available now in Live!

Get 1:1 Help Now