Solved

JOIN Question

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL with ODBC 5 34
always on switch back after failover 2 31
what are the unique tables in SQL master database 5 58
Update a text value in another table 10 37
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

808 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