get all descendants where parent has constraint using JCR-SQL2

[mike osterhout <mi...@gmail.com>]

I have a tree that looks something like this:

-cms
    -posts
        -sub-post1
            -content1
            -content2
        -sub-post2
            -content1
            -content2
    -posts1
        -sub-post1
            -content1
            -content2
        -sub-post2
            -content1
            -content2

My "posts" node has a flag for being "published" or not. I want to be able
to select all of the "content" where it's parent "posts" node has this
"published" flag set to true. What is the best way to make this happen with
JCR-SQL2?

One thing that looked ok was to use INNER JOIN with ISDESCENDANTNODE two
times like this.

SELECT content.*
FROM [nt:unstructured] AS content
INNER JOIN [nt:unstructured] AS sub ON ISCHILDNODE(content, sub)
INNER JOIN [nt:unstructured] AS posts ON ISCHILDNODE(sub, posts)
WHERE ISCHILDNODE(content, [/cms/posts])
AND posts.published = true