How do I add a boolean column to an SQL result set based on comparison with a second table

Essentially I have the first part of the question answered and here is the SQL statement I have so far...

SELECT DbaRolePrivs.GRANTEE,
       DbaRolePrivs.GRANTED_ROLE,
       ApplicationRoleDefinition.ROLE,
       ApplicationRoledefinition.description_tx, 
       CASE
         WHEN GRANTED_ROLE = ROLE AND GRANTEE = USERNAME THEN
           'T'
         ELSE
           'F'
       END AS checkMark
FROM DBA_ROLE_PRIVS DbaRolePrivs, APPLICATION_ROLE_DEFINITION ApplicationRoleDefinition

Here is the logic I a开发者_如何学Cm trying to perform in plain english.

"Given a list of all roles and their descriptions, mark each role as TRUE or FALSE based on if the current selected USERNAME has been granted that role based on their own GrantedRoles list."

Right now it is of course returning a double list since the "case when" section compares each of the granted roles with the total list of roles. (I say duplicate because the test user only has two roles, in the current SQL query it will return a row per granted role for every possible role.

I've barely worked with SQL so I'm more used to C++, Objective-C, etc. Really all I need in the "case when" section is to check if the current ROLE exists at all in the GRANTED_ROLE column. I've seen the EXISTS function but that seems to be meant solely for result sets such as "WHERE EXISTS."

Thanks in advance, I know it's probably simple for anyone whose worked with databases often, I've only just started.

You need to outer join and then test whether the join was successful or not. If the outer join fails a row is still returned but all the DbaRolePrivs columns will be null

SELECT  DbaRolePrivs.GRANTEE,
        DbaRolePrivs.GRANTED_ROLE,
        ApplicationRoleDefinition.ROLE,
        ApplicationRoledefinition.description_tx,
        NVL2( DbaRolePrivs.GRANTED_ROLE, 'T', 'F' ) AS checkMark
FROM    DBA_ROLE_PRIVS DbaRolePrivs
    ,   APPLICATION_ROLE_DEFINITION ApplicationRoleDefinition
WHERE   ApplicationRoleDefinition.ROLE = DbaRolePrivs.GRANTED_ROLE (+)
AND     USERNAME                       = DbaRolePrivs.GRANTEE      (+)