tricky select query, need assistance

Question

I have a table named psroleuser, which haves coulmns rolename, roleuser.

For each rolename, there are a set of roleuser, for eg rolename A can have 20 roleusers, rolename B can have 30 roleusers.

Now, I want to select all roles i.e rolenames which haves 10 roleusre or less.

How to do it.? I understnad this is very elementry in database, but so am I..plus can anyone suggest good oracle,sql reads to start with.

Thank you.

Answer 1

You didn't share the columns in those tables, and I'm not directly familiar with PeopleSoft, but it sounds like there's also a User table (or similar), and the roleuser table maps those user records to rolename records. If that's true, the table will have columns with names something like RoleID and UserID. Using those columns as examples, you'll end up with something like this:

SELECT RoleID
FROM RoleUser
GROUP BY RoleID
HAVING COUNT(UserID) <= 10