we'd always recommend that you dont write direct sql as there is no guarantee that a future upgrade does not make database changes that break your sql - instead you should use the relevant DotNetNuke API e.g. RoleController/UserController.  That said if you want to take the risk the best thing to do is to download the ERD diagrams that Chris Smith creates to see the structure of the tables - https://www.nddllc.net/Documents

The basic underlying relationship between the tables looks like

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

FROM UserRoles INNER JOIN

Roles ON UserRoles.RoleID = Roles.RoleID INNER JOIN

Users ON UserRoles.UserID = Users.UserID

You'll have to amend that to work as you want e.g. if you're passing roleid's you can cut the roles table out altogether, if you're working with rolenames you'll have to use that table and supply portalid's as well etc.

SELECT Users.FirstName, Users.LastName, Users.Email,UserRoles.RoleID

FROM UserRoles INNER JOIN

Users ON UserRoles.UserID = Users.UserID

WHERE (UserRoles.RoleID = 1) AND

(UserRoles.RoleID <> 2)

---

Buy the new Professional DNN7: Open Source .NET CMS Platform book Amazon US

Hi,

Okay, in trying to get the correct results, I have 500 users in the registerd user role(ROLE 1) and 300 users in SomeRole(ROLE 2).

When using your query to just find users in role I get the correct results.

SELECT Users.FirstName, Users.LastName, Users.Email,UserRoles.RoleID

FROM UserRoles INNER JOIN

Users ON UserRoles.UserID = Users.UserID

WHERE (UserRoles.RoleID = 1)  QUERY RETURNS 500 user as expected.

SELECT Users.FirstName, Users.LastName, Users.Email,UserRoles.RoleID

FROM UserRoles INNER JOIN

Users ON UserRoles.UserID = Users.UserID

WHERE (UserRoles.RoleID = 2) QUERY RETURNS 300 users as expected.

All of my users should be a registerd user but not all of them are in the 'SomeRole'.  So I was trying to build a query that would give me all of the registered users that WERE NOT in the 'SomeRole', which should be 200 users.  But the query below always returns 500 users. 

SELECT Users.FirstName, Users.LastName, Users.Email,UserRoles.RoleID

FROM UserRoles INNER JOIN

Users ON UserRoles.UserID = Users.UserID

WHERE (UserRoles.RoleID = 1) AND

(UserRoles.RoleID <> 2) QUERY RETURNS 500 users.

I'm not sure why this happening.  Any ideas.  Again thanks for your help, it's very much appreciated.

Jerry