Hey Guys,

We are using SQLGRid module so we can display some user information. I have limited SQL knowledge and wondering if someone can help me with a statement. We are using DNN 3.2.

We are wanting to display all the Contact and Mailing information for all those who are part of the Registered Users role, but not part of the "Customer User Role" that we created. Basically we have a whole lot of members that are "registered", however have not paid subscriptions to joint the customer role we created. We now wanted to contact all these users.

Regards, Jay

Thanks Rodney, that site is great. The one closest is this statement, but I just need to say "but not R.Rolename = "MyCustomerRole"

SELECT U.Username, U.Firstname, U.Lastname, R.Rolename, AM.Email
FROM Users U
INNER JOIN UserRoles UR on U.Userid = UR.Userid
INNER JOIN Roles R on UR.Roleid = R.Roleid
INNER JOIN Aspnet_users AU on U.Username = AU.Username
INNER JOIN Aspnet_membership AM on AU.Userid = AM.Userid
WHERE R.Rolename = 'Registered Users'
ORDER by U.Lastname

Cheers, Jay

Uhhh... My blood to coffee ratio must not have been high enough yet when I wrote that.  I don't think that query will work since a user can be in more than one role.  R.RoleName can only have one value for any given row of data.  So if R.RoleName = 'Registered Users' it obviously <> 'MyCustomerRole'.  Perhaps we need a corelated sub-query in the where clause.  Something like this:

SELECT U.Username, U.Firstname, U.Lastname, R.Rolename, AM.Email
FROM   Users U
INNER  JOIN UserRoles UR on U.Userid = UR.Userid
INNER  JOIN Roles R on UR.Roleid = R.Roleid
INNER  JOIN Aspnet_users AU on U.Username = AU.Username
INNER  JOIN Aspnet_membership AM on AU.Userid = AM.Userid
WHERE  R.Rolename = 'Registered Users' AND 
       U.Username NOT IN (
          <select statement to get the usernames of all users in the MyCustomerRole role>
       )
ORDER by U.Lastname

If no one else beats me to it I'll work it up tonight when I get home.  My boss is looking over my shoulder tapping his foot.  Just kidding.  He's not even here yet but since I'm at work I've already spent too much time doing non-work related stuff.

---

Bryan Johns
K4GDW

"There are 10 types of people. Those who understand binary and those who do not."