Give this one a shot:

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.UserID NOT IN (
    select sur.UserID from UserRoles sur
    inner join Roles sdr on sdr.RoleID = sur.RoleID
    where sdr.RoleName = 'MyCustomerRole'
       )
ORDER by U.Lastname

---

Bryan Johns
K4GDW

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

Hey Guys,

I need to produce another sql statement which displays users who have been assigned a custom role, similar to the statement below, however I was needing to display the following fields:

First Name, Last Name, Unit Number, Street, City, Region, Country, Post Code, Telephone, Email

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'  

Regards, Jay