How to write query in sql2 for matching properties of parent node and child nodes simultaneously. | Community
Skip to main content
__96
__96
Level 4
February 1, 2016
Solved

How to write query in sql2 for matching properties of parent node and child nodes simultaneously.

  • February 1, 2016
  • 6 replies
  • 4772 views

I have a scenario where I have to match 2-3 properties of parent node and 2 properties of some child node under that parent node simultaneously. I am unable to achieve that only one condition runs at one time.

SELECT * FROM [nt:unstructured] AS parent WHERE ISDESCENDANTNODE(parent,[/etc/employees/employeedata]) AND parent.[bondStartDate] < CAST('2016-02-01T00:00:00.000+05:30' AS DATE) and parent.[bondEndDate] > CAST('2016-02-01T00:00:00.000+05:30' AS DATE) AND parent.[depts] = "purchase".

The structure of the Node is like :

                   etc/employees/employeedata

                                           -- employee1 (bondStartDate, bondEndDate, DOJ, Designation etc properties)

                                                        -- bonddetails

                                                                     -- 0 (depts, tenure etc properties)

                                                                     --- 1 (depts, tenure etc properties)

 

Now if I run 

SELECT * FROM [nt:unstructured] AS parent WHERE ISDESCENDANTNODE(parent,[/etc/employees/employeedata]) AND parent.[bondStartDate] < CAST('2016-02-01T00:00:00.000+05:30' AS DATE) and parent.[bondEndDate] > CAST('2016-02-01T00:00:00.000+05:30' AS DATE)

it gives me the node 

etc/employees/employeedata/employee1

If I run below :

SELECT * FROM [nt:unstructured] AS parent WHERE ISDESCENDANTNODE(parent,[/etc/employees/employeedata]) AND parent.[depts] = "purchase"

it returns etc/employees/employeedata/employee1/bonddetails/0

But I want to match both the criteria simultaneously and return the parent node i.e. etc/employees/employeedata/employee1 or  etc/employees/employeedata/employee1/bonddetails/0 whatever is possible.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Kunal_Gaba_

$@^^!R wrote...

kunal23 wrote...

Try the following query-

  1. SELECT parent.* FROM [nt:unstructured] AS parent INNER JOIN [nt:unstructured] AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent, /etc/employees/employeedata) AND parent.[bondStartDate] < CAST('2016-02-01T00:00:00.000+05:30' AS DATE) and parent.[bondEndDate] > CAST('2016-02-01T00:00:00.000+05:30' AS DATE) AND (child.[depts] = 'purchase'

 

Hi Kunal,

Tried this way but this also isn't working. Again if I remove the last part i.e. child.[depts] it returns result for first criteria. And if I remove parent's criteria and only keep child.[depts] then it returns the result for only this criteria. Any other way which can make it work? If you want I can send you the package of node structure.

Thanks.

 

Hi Samir, 

I got the package from Scott and was able to fix the query for you. Try the following query- 

SELECT parent.* FROM [nt:unstructured] AS parent INNER JOIN [nt:unstructured] AS child ON ISDESCENDANTNODE(child,parent) WHERE ISDESCENDANTNODE(parent, [/etc/employees/employeedata]) AND parent.[bondStartDate] < CAST('2016-02-01T00:00:00.000+05:30' AS DATE) and parent.[bondEndDate] > CAST('2016-02-01T00:00:00.000+05:30' AS DATE) AND child.[depts] = 'purchase'

Thanks,

Kunal

6 replies

Kunal_Gaba_
Kunal_Gaba_
February 1, 2016

Try the following query-

SELECT parent.* FROM [nt:unstructured] AS parent INNER JOIN [nt:unstructured] AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent, /etc/employees/employeedata) AND parent.[bondStartDate] < CAST('2016-02-01T00:00:00.000+05:30' AS DATE) and parent.[bondEndDate] > CAST('2016-02-01T00:00:00.000+05:30' AS DATE)  AND (child.[depts] = 'purchase'
smacdonald2008
smacdonald2008
Level 10
February 1, 2016

Can you please package up this sample data and send to me (scottm@adobe.com).

I would need to code against this to provide more information. I want to see your  JCR structure (     etc/employees/employeedata) including the properties. 

__96
__96Author
Level 4
February 1, 2016

kunal23 wrote...

Try the following query-

  1. SELECT parent.* FROM [nt:unstructured] AS parent INNER JOIN [nt:unstructured] AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent, /etc/employees/employeedata) AND parent.[bondStartDate] < CAST('2016-02-01T00:00:00.000+05:30' AS DATE) and parent.[bondEndDate] > CAST('2016-02-01T00:00:00.000+05:30' AS DATE) AND (child.[depts] = 'purchase'

 

Hi Kunal,

Tried this way but this also isn't working. Again if I remove the last part i.e. child.[depts] it returns result for first criteria. And if I remove parent's criteria and only keep child.[depts] then it returns the result for only this criteria. Any other way which can make it work? If you want I can send you the package of node structure.

Thanks.

Kunal_Gaba_
Kunal_Gaba_
February 1, 2016

The query which I shared should work because it is does the inner join of parent with child node and then it filters based on the criteria. It will return you the parent node which meets the date range and has a child of purchase department Please share the package structure so that I can test it locally. 

Kunal_Gaba_
Kunal_Gaba_Accepted solution
February 1, 2016

$@^^!R wrote...

kunal23 wrote...

Try the following query-

  1. SELECT parent.* FROM [nt:unstructured] AS parent INNER JOIN [nt:unstructured] AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent, /etc/employees/employeedata) AND parent.[bondStartDate] < CAST('2016-02-01T00:00:00.000+05:30' AS DATE) and parent.[bondEndDate] > CAST('2016-02-01T00:00:00.000+05:30' AS DATE) AND (child.[depts] = 'purchase'

 

Hi Kunal,

Tried this way but this also isn't working. Again if I remove the last part i.e. child.[depts] it returns result for first criteria. And if I remove parent's criteria and only keep child.[depts] then it returns the result for only this criteria. Any other way which can make it work? If you want I can send you the package of node structure.

Thanks.

 

Hi Samir, 

I got the package from Scott and was able to fix the query for you. Try the following query- 

SELECT parent.* FROM [nt:unstructured] AS parent INNER JOIN [nt:unstructured] AS child ON ISDESCENDANTNODE(child,parent) WHERE ISDESCENDANTNODE(parent, [/etc/employees/employeedata]) AND parent.[bondStartDate] < CAST('2016-02-01T00:00:00.000+05:30' AS DATE) and parent.[bondEndDate] > CAST('2016-02-01T00:00:00.000+05:30' AS DATE) AND child.[depts] = 'purchase'

Thanks,

Kunal

__96
__96Author
Level 4
February 2, 2016

kunal23 wrote...

$@^^!R wrote...

kunal23 wrote...

Try the following query-

  1. SELECT parent.* FROM [nt:unstructured] AS parent INNER JOIN [nt:unstructured] AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent, /etc/employees/employeedata) AND parent.[bondStartDate] < CAST('2016-02-01T00:00:00.000+05:30' AS DATE) and parent.[bondEndDate] > CAST('2016-02-01T00:00:00.000+05:30' AS DATE) AND (child.[depts] = 'purchase'

 

Hi Kunal,

Tried this way but this also isn't working. Again if I remove the last part i.e. child.[depts] it returns result for first criteria. And if I remove parent's criteria and only keep child.[depts] then it returns the result for only this criteria. Any other way which can make it work? If you want I can send you the package of node structure.

Thanks.

 

Hi Samir, 

I got the package from Scott and was able to fix the query for you. Try the following query- 

  1. SELECT parent.* FROM [nt:unstructured] AS parent INNER JOIN [nt:unstructured] AS child ON ISDESCENDANTNODE(child,parent) WHERE ISDESCENDANTNODE(parent, [/etc/employees/employeedata]) AND parent.[bondStartDate] < CAST('2016-02-01T00:00:00.000+05:30' AS DATE) and parent.[bondEndDate] > CAST('2016-02-01T00:00:00.000+05:30' AS DATE) AND child.[depts] = 'purchase'

Thanks,

Kunal

 

 

Hi Kunal,

Thanks, this is working perfectly as expected. The difference I see is that you have used ISDESCENDANTNODE instead of ISCHILDNODE. Don't know the difference between the two. But anyways thanks again.

Thanks.

Terms & ConditionsAccessibility statement

Loading footer...