Is there an SQL2
query that is identical to the following XPath
query:
/jcr:root/content/ancestor//parent/jcr:content
It should return the following nodes, for example:
/content/ancestor/a/b/c/parent/jcr:content
/content/ancestor/a/parent/jcr:content
/content/ancestor/parent/jcr:content
But not the following nodes:
/content/xxx/a/b/c/parent/jcr:content
/content/xxx/a/parent/jcr:content
/content/xxx/parent/jcr:content
Following should work:
SELECT * FROM [nt:base] AS s
INNER JOIN [nt:base] AS parent
ON ISCHILDNODE(s, parent)
WHERE
ISDESCENDANTNODE(s, [/content/ancestor]) AND
NAME(parent) = 'parent' AND
NAME(s) = 'jcr:content'
If the type of s
is cq:PageContent
and type of parent
is cq:Page
you may use this information to make the query faster:
SELECT * FROM [cq:PageContent] AS s
INNER JOIN [cq:Page] AS parent
ON ISCHILDNODE(s, parent)
WHERE
ISDESCENDANTNODE(s, [/content/ancestor]) AND
NAME(parent) = 'parent' AND
NAME(s) = 'jcr:content'