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

[__96]

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.

[Kunal_Gaba_]

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]

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]

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_]

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_ · Accepted solution]

$@^^!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]

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.