extracting account details of a user with no values for a particular entitlement_type

[soumik_das]

Hi,

I want to extract the account details of a user with no entitlement values for a particular type of entitlement for a particular endpoint. I have written an SQL code for that but I am not getting the details of the account which doesn't have that particular type of entitlement for a particular endpoint. Please can you help me out here. I am attaching the code below:

SELECT U.username ,
U.customproperty3 ,
U.departmentnumber ,
U.jobdescription ,
U.startdate ,
U.DEPARTMENTNAME ,
a.name,
a.status as "Current_Status",
date(a.created_on),
a.endpointkey
FROM users U join user_accounts ua on U.userkey=ua.userkey join accounts a on ua.accountkey=a.accountkey join entitlement_types e on a.endpointkey=e.endpointkey where a.accountkey not in (select accountkey from account_entitlements1) and a.endpointkey=25

[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);