Query to fetch those roles which dont have entitlements tagged to particular security systems

[gagan94]

HI Team,

I need to get those roles which dont have any entitlements tagged to it for particular security systems. 
I tried to get data but it is showing no data found, I am able to get the roles listed under security systems,endpoints but not those roles which dont have any entitlements tagged to it .

Can anybody help in this query what need to be add.

select r.role_name,ep.endpointname from endpoints ep,entitlement_values ev,entitlement_types et,role_entitlements re,roles r,securitysystems ss where re.rolekey=r.rolekey and re.ENTITLEMENT_VALUEKEY=ev.ENTITLEMENT_VALUEKEY and ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY and et.endpointkey=ep.endpointkey and ep.SECURITYSYSTEMKEY=(select ss.SYSTEMKEY from securitysystems where securitysystems.SYSTEMNAME ='endpointname') and ev.ENTITLEMENT_VALUEKEY NOT IN(select re.ENTITLEMENT_VALUEKEY from role_entitlements re where ev.ENTITLEMENT_VALUEKEY=re.ENTITLEMENT_VALUEKEY)

Thanks in Advance

[rushikeshvartak]

select r.role_name,endpointkey from roles r where rolekey not in (select rolekey from role_entitlements re where re.rolekey=r.rolekey)