select from a table minus other table?

[majid nakit]

Hi,

I have two select statment that are working fine getting data from two tables :

To get all roles:

SELECT roles

FROM dba_roles

WHERE ROLE LIKE 'COEUS%' AND ROLE NOT IN ('COEUSWEB', 'COEUSUSER');


To get all roles for user

SELECT roles

FROM dba_role_privs

WHERE grantee = upper(as_userid);


How to have a select statment that have all roles from dba_roles table minus the roles dba_role_privs for a user ?

Thanks, your help is appreciated.
[ October 27, 2008: Message edited by: Bear Bibeault ]

[Jeanne Boyarsky]

How about:

SELECT roles
FROM dba_roles
WHERE ROLE NOT IN (
SELECT roles
FROM dba_role_privs);