How to filter SQL2 query based on a property in Parent node (2nd level)

[mrudul]

Hi,

I have a query which returns the dam:Asset nodes. However, I have to add a clause to filter out further based on the property in 2nd level of parent node (sling:Folder)

Present Query looks like this: SELECT PATH FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE(a,[/content/dam/my-org/testdev25ex])

The result set of above should be further filtered by checking a jcr property in 2nd level of parent folder which is always a sling:Folder
I can always work on results and filter out programmatically. However, i want to apply this filter in the query itself.

Anyone have idea how can we achieve this ?

Node structure would look like this:

Marked images in the green should basically filtered further based on the property in second level of parent node which is testdev25ex 

[BrianKasingli]

I have not tested this, but would this work?

SELECT asset.[jcr:path]
FROM [dam:Asset] AS asset
JOIN [nt:base] AS parentFolder ON ISCHILDNODE(asset, parentFolder)
WHERE ISDESCENDANTNODE(asset, '/content/dam/my-org/testdev25ex')
AND parentFolder.[sling:resourceType] = 'sling:Folder'
AND parentFolder.[yourProperty] = 'Your Value'

[ManviSharma · Accepted solution]

Hi ,

To filter based on a property in the 2nd level of the parent node (sling:Folder), use the JOIN clause in your query:

SELECT asset.[jcr:path] FROM [dam:Asset] AS asset
JOIN [sling:Folder] AS folder ON ISCHILDNODE(asset, folder)
WHERE ISDESCENDANTNODE(asset, [/content/dam/my-org/testdev25ex])
AND folder.[propertyName] = 'desiredValue'