Products

Solutions

Resources

Partners

Community

About

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser...SQL Query for Users in RolesSQL Query for Users in Roles
Previous
 
Next
New Post
10/14/2011 12:32 AM
 
Hi,

I'm trying to develop a SQL query for a report.  I need to check if a user is in one specific role, while not in another specific role..  The query should output the users first name, last name, email.   Any help/guidance with this question is very much appreciated.

Thanks
Jerry
 
New Post
10/14/2011 3:19 AM
 
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
 
New Post
10/23/2011 4:14 PM
 
Cathal,

Thank-you very much for the direction it helped me accomplish what I was trying to do.

Jerry
 
New Post
10/23/2011 5:17 PM
 
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

 
New Post
10/24/2011 10:26 AM
 
SELECT Users.FirstName, Users.LastName, Users.Email,UserRoles.RoleID

FROM UserRoles INNER JOIN

Users ON UserRoles.UserID = Users.UserID

WHERE (UserRoles.RoleID = 1) and Users.userID not IN (SELECT UserID FROM UserRoles WHERE RoleID = 2)


Cheers from Germany,
Sebastian Leupold

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
Previous
 
Next
HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser...SQL Query for Users in RolesSQL Query for Users in Roles


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out