Okay, I know how to list my members based on role but I don't know how to limit the list to just those users who are only a registered user and not part of any other role.

Here is the script I'm using (Thanks to a poster on the net!) I know very little about SQL and could really use anyone's help.

Okay, I found the following SQL script online (THANK YOU) but I wish to modify it so it returns the registered uses who are not assigned any other roles. The part I modified was to look at the RoleID in UserRoles, sum them up and return only those queries that = 1.

I am a novice in SQL and could really use the help, the code is below and my additions are in red. Thanks!!



SELECT Users.Username, Users.LastName, Users.FirstName, Users.Email,

(SELECT PropertyValue FROM UserProfile WHERE
UserProfile.UserID = Users.UserID AND
UserProfile.PropertyDefinitionID = 26
) As Street,

(SELECT PropertyValue FROM UserProfile WHERE
UserProfile.UserID = Users.UserID AND
UserProfile.PropertyDefinitionID = 27
) As City,

(SELECT PropertyValue FROM UserProfile WHERE
UserProfile.UserID = Users.UserID AND
UserProfile.PropertyDefinitionID = 30
) As Zip,

(SELECT PropertyValue FROM UserProfile WHERE
UserProfile.UserID = Users.UserID AND
UserProfile.PropertyDefinitionID = 31
) As Home,

(SELECT PropertyValue FROM UserProfile WHERE
UserProfile.UserID = Users.UserID AND
UserProfile.PropertyDefinitionID = 32
) As Cell,

(SELECT UserRoles.UserId, SUM(UserRoles.RoleId) <<== This is the block I'm trying to use to limit the returns.
FROM UserRoles
GROUP BY UserRoles.UserID
HAVING SUM(UserRoles.RoleId)= 1) as Role

FROM Users INNER JOIN
UserPortals ON Users.UserID = UserPortals.UserId
INNER JOIN
UserRoles ON Users.UserID = UserRoles.UserID

WHERE UserPortals.PortalId = 0 AND
Role = 1

ORDER BY Users.Lastname

Thanks.

Here is the unmodified code for anyone who might want to use it...just change the PropertyDefinitionID to your specific id and UserRoles.UserId to the role you want returned.

SELECT Users.Username, Users.LastName, Users.FirstName, Users.Email,

(SELECT PropertyValue FROM UserProfile WHERE
UserProfile.UserID = Users.UserID AND
UserProfile.PropertyDefinitionID = 26
) As Street,

(SELECT PropertyValue FROM UserProfile WHERE
UserProfile.UserID = Users.UserID AND
UserProfile.PropertyDefinitionID = 27
) As City,

(SELECT PropertyValue FROM UserProfile WHERE
UserProfile.UserID = Users.UserID AND
UserProfile.PropertyDefinitionID = 30
) As Zip,

(SELECT PropertyValue FROM UserProfile WHERE
UserProfile.UserID = Users.UserID AND
UserProfile.PropertyDefinitionID = 31
) As Home,

(SELECT PropertyValue FROM UserProfile WHERE
UserProfile.UserID = Users.UserID AND
UserProfile.PropertyDefinitionID = 32
) As Cell

FROM Users INNER JOIN
UserPortals ON Users.UserID = UserPortals.UserId
INNER JOIN
UserRoles ON Users.UserID = UserRoles.UserID

WHERE UserPortals.PortalId = 0 AND
UserRoles.RoleID = 3

Sorry for the multiple posts but I'd thought I'd add my thougth pattern so you might better understand what I'm trying to do...

Based on the RoleID in UsersRoles, the Registered Role id is 1. If that is the ONLY role assigned to a user, then the SUM of all their roles is 1 and that is how I know they are not assigned other roles.

Thanks.

Hi, you could try something like this. In my case here, 15 is the roleid of the registered users role for portal 0. Basically I'm limiting the user list to only those users who have exactly 1 role and then adding a criteria that they are in the registered role.

select u.* from users u
where
(select count(*) from userroles where userid = u.userid) = 1 -- one role
and
u.userid in (select userid from userroles where userid = u.userid and roleid = 15) -- in registed role

Thank you so much for your reply. The works great but is there anyway to incorporate that into the code above?

Try this:

SELECT Users.Username, Users.LastName, Users.FirstName, Users.Email,

(SELECT PropertyValue FROM UserProfile WHERE
UserProfile.UserID = Users.UserID AND
UserProfile.PropertyDefinitionID = 26
) As Street,

(SELECT PropertyValue FROM UserProfile WHERE
UserProfile.UserID = Users.UserID AND
UserProfile.PropertyDefinitionID = 27
) As City,

(SELECT PropertyValue FROM UserProfile WHERE
UserProfile.UserID = Users.UserID AND
UserProfile.PropertyDefinitionID = 30
) As Zip,

(SELECT PropertyValue FROM UserProfile WHERE
UserProfile.UserID = Users.UserID AND
UserProfile.PropertyDefinitionID = 31
) As Home,

(SELECT PropertyValue FROM UserProfile WHERE
UserProfile.UserID = Users.UserID AND
UserProfile.PropertyDefinitionID = 32
) As Cell

FROM Users INNER JOIN
UserPortals ON Users.UserID = UserPortals.UserId
INNER JOIN
UserRoles ON Users.UserID = UserRoles.UserID

WHERE
(select count(*) from userroles where userid = users.userid) = 1 -- one role
and
users.userid in (select userid from userroles where userid = users.userid and roleid = 3) -- in registered role

Thank you! I had to drop the last line to get the list users who were ONLY assigned the "Registered" role but it did the trick.

I also had a need to filter out all but those users who had yet to verify their email accounts. Here is the code I added to give me that list:

Add between the last INNER JOIN and replace the WHERE:

INNER JOIN
AspNet_Membership ON Users.EMail = AspNet_Membership.EMail

WHERE
AspNet_Membership.Email = Users.Email
AND
AspNet_Membership.IsApproved='False'

You were extremely helpful and I appreciate it. This has helped me find my way around SQL and my database.

Thanks again!

You're very welcome.

I'm not sure why you had to get rid of that last line. It's the line that limits only those in just the registered role. You might have to change the 3 to what ever the registered role is for your particular portal id. In most cases, if the users only have 1 role it will be the registered role but I wanted to put that in there just in case.

Yes, you're correct. I forget to change the registered role Id to the correct number.

Once again, thank you very much.