How to get query for child and parents entitlement together in single query

[Community_User]

Originally posted on November 29 2021 at 04:18 UTC

Hi Team,

Can somebody please help how to query out to get details for child and parent entitlement together in Single Query as mentioned in the below screen-shot?

[Community_User]

Originally posted on November 29 2021 at 06:39 UTC

Hello,

Where are you looking to get this information onto a single view? Child entitlements will directly be shown under entitlement hierarchy for an account as below :

Regards,

Sahaj

[Community_User]

Originally posted on November 29 2021 at 07:07 UTC

Hi Sahaj,

We expecting SQL Query to get the information for child and parent entitlement together in Single query and we using below query but it is not working as expected: 

select ev.entitlement_value as parentent from entitlement_values ev, entitlements2 e
where ev.ENTITLEMENT_VALUEKEY = e.ENTITLEMENT_VALUE1KEY and e.ENTITLEMENT_VALUE2KEY = '28'  

Regards,

Parthiban S

[Community_User]

Originally posted on November 29 2021 at 22:30 UTC

Hello Parthiban,

The query you have written seem to be correct to pull in all the parent entitlements that is tied to your child entitlement whose entValKey is 28.

Regards,

Avinash Chhetri

[Community_User · Accepted solution]

Originally posted on November 30 2021 at 05:37 UTC

Hi Parthiban,

You can try something like the below query :

Select distinct parent.entitlement_value as parentEntitlement,child.ENTITLEMENT_VALUE as childEntitlement
from entitlement_values parent
join entitlements2 ev2 on parent.ENTITLEMENT_VALUEKEY=ev2.ENTITLEMENT_VALUE1KEY
join entitlement_values child on child.ENTITLEMENT_VALUEKEY=ev2.ENTITLEMENT_VALUE2KEY
where parent.entitlementtypekey=20 

Always ensure you limit the dataset by adding the appropriate where clause else the data would be huge and might cause issues. In the above example, i have made the query send parent child entitlements where the parent entitlement is always of a particular entitlement type.

Regards,

Sahaj