Getting the account details of a user with no entitlement values present in the account

[soumik_das]

Hi,
I want to extract the details of an account which doesn't have any entitlements. I am providing entitlement_value as NULL but the query is returning no results even though there is a user with no entitlements. I am attaching the query for you reference.

SELECT U.username ,
U.customproperty3 ,
U.departmentnumber ,
U.jobdescription ,
U.startdate ,
U.DEPARTMENTNAME ,
A.name,
A.status  ,
A.created_on  ,
EV.entitlement_value,
et.entitlementname 
FROM users U,
accounts A,
account_entitlements1 AE,
entitlement_values EV,
entitlement_types et
WHERE ( U.customproperty3 = 65
OR U.customproperty3 = 01 )
AND A.endpointkey = 5
AND U.statuskey = 1
AND A.STATUS=1
AND A.accountid = U.customproperty28
AND A.created_on >= DATE_SUB(CURDATE(), INTERVAL 100 DAY)
AND AE.entitlement_valuekey = EV.entitlement_valuekey
AND A.accountkey = AE.accountkey
AND et.entitlementtypekey = ev.entitlementtypekey
AND (et.entitlementname = "Role" or et.entitlementname = "Profile" )
AND EV.entitlement_value is NULL

Thanks and regards,
Soumik Das

[armaanzahir]

Hi @soumik_das 

If an account does not have any entitlements, it would have no entry in the account_entitlements1 table hence you can try the below query to fetch such users:

SELECT U.username ,
U.customproperty3 ,
U.departmentnumber ,
U.jobdescription ,
U.startdate ,
U.DEPARTMENTNAME ,
A.name,
A.status ,
A.created_on
FROM users U join user_accounts ua on U.userkey=ua.userkey join accounts a on ua.accountkey=a.accountkey where a.accountkey not in (select accountkey from account_entitlements1)

Database Schema Reference (saviyntcloud.com)

[soumik_das]

Hi @armaanzahir ,

Please can you let me know for a particular entitlement type how can we get the account details where there is no  values of that particular entitlement type?

Thanks and regards,
Soumik Das

[dgandhi]

Can you try this?

SELECT U.username ,
U.customproperty3 ,
U.departmentnumber ,
U.jobdescription ,
U.startdate ,
U.DEPARTMENTNAME ,
A.name,
A.status ,
A.created_on
FROM users U
join user_accounts ua
on U.userkey=ua.userkey
join accounts a
on ua.accountkey=a.accountkey
join endpoints e
on a.endpointkey=e.endpointkey
join entitlement_types et
on e.endpointkey=et.endpointkey
where a.accountkey not in (select accountkey from account_entitlements1)
and et.entitlementname='Name'

[rushikeshvartak]

SELECT U.username,
       U.customproperty3,
       U.departmentnumber,
       U.jobdescription,
       U.startdate,
       U.departmentname,
       A.name,
       A.status,
       A.created_on,
       EV.entitlement_value,
       et.entitlementname
FROM   users U
       join accounts A
         ON A.accountid = U.customproperty28
       left join account_entitlements1 AE
              ON A.accountkey = AE.accountkey
       left join entitlement_values EV
              ON AE.entitlement_valuekey = EV.entitlement_valuekey
       left join entitlement_types et
              ON EV.entitlementtypekey = et.entitlementtypekey
WHERE  ( U.customproperty3 = 65
          OR U.customproperty3 = 01 )
       AND A.endpointkey = 5
       AND U.statuskey = 1
       AND A.status = 1
       AND ( et.entitlementname = "role"
              OR et.entitlementname = "profile" )
       AND EV.entitlement_value IS NULL
       AND A.created_on >= Date_sub(Curdate(), interval 100 day);