Need help in query to get list of entitlements without users

[Sharada]

Hi team,

Need help in query to get list of entitlements without users. 

Regards,

Sharada

[Abhishek0406 · Accepted solution]

select
ep.ENDPOINTNAME as application_name,
et.entitlementname as entitlement_type_name,
ev.entitlement_value,
a.name as account_name,
u.username
from
endpoints ep
left join entitlement_types et on et.endpointkey = ep.endpointkey
left join entitlement_values ev on ev.entitlementtypekey = et.entitlementtypekey
left join account_entitlements1 ae on ae.entitlement_valuekey = ev.entitlement_valuekey
left join accounts a on a.accountkey = ae.accountkey
left join user_accounts ua on ua.accountkey = a.accountkey
left join users u on u.userkey = ua.userkey
where
ev.status = 1 and a.name is null

[rushikeshvartak · Accepted solution]

SELECT ep.endpointname    AS application_name,
       et.entitlementname AS entitlement_type_name,
       ev.entitlement_value,
       a.NAME             AS account_name,
       u.username
FROM   endpoints ep
       LEFT JOIN entitlement_types et
              ON et.endpointkey = ep.endpointkey
       LEFT JOIN entitlement_values ev
              ON ev.entitlementtypekey = et.entitlementtypekey
       LEFT JOIN account_entitlements1 ae
              ON ae.entitlement_valuekey = ev.entitlement_valuekey
       LEFT JOIN accounts a
              ON a.accountkey = ae.accountkey
       LEFT JOIN user_accounts ua
              ON ua.accountkey = a.accountkey
       LEFT JOIN users u
              ON u.userkey = ua.userkey
WHERE  ev.status = 1
       AND u.userkey IS NULL
   

[Sharada]

Thank you @Abhishek0406  and @rushikeshvartak . The query worked