This is actually pretty simple to do with JCR-SQL2, once you realize that you have to use a JOIN because you want the query to apply to two different sets of nodes: the "test:managedElement" and their referenced "test:second" nodes. Here's a query that should work:
SELECT man.* FROM [test:managedElement] AS man JOIN [test:second] as sec ON man.second = sec.[jcr:uuid] WHERE sec.myAttribue = 'hello'
This query will return all of the properties defined on "test:managedElement" (including properties defined on supertypes) that reference (via the "second" property) a "test:second" node with a "myAttribute" property value of "hello". The "ON man.second = sec.[jcr:uuid]" clause is what constrains the 'sec' nodes to be referenced by nodes in the 'man'.
Of course, rather than selecting all properties, you may want to select just the properties you're interested in (which can include the properties of the "second" nodes). For example, here's a modified version of the query that selects just a few properties from each node:
SELECT man.[jcr:uuid], man.myAttribute, man.testAttributeB, sec:myAttribute, sec:[jcr:uuid] FROM [test:managedElement] AS man JOIN [test:second] as sec ON man.second = sec.[jcr:uuid] WHERE sec.myAttribue = 'hello'
BTW, the "myAttribute" and "testAttributeB" property definitions on "test:second" aren't really needed, are they? They appear to be just redefining the property definitions of the same name and type inherited from the "test:managedElement" supertype. Technically it will probably work as you expect, but semantically it is maybe not correct.
Hope that helps!
thanks a lot! The queries you suggested work fine.
And sorry for bothering you with such a dummy question.
No problem at all! And no question is a dumb question.
But one more question: Is there probably another way to achive the same by using REFERENCE?
Yes, there are multiple queries that actually would produce the results you're interested in. But that's true of SQL, too. The JOIN query I suggested earlier IMO is the clearest way to describe the query you want to use.
The ModeShape REFERENCE extension is non-standard, and it's not really even beneficial in your case because you know the name of the reference property a priori. The reason we added REFERENCE(...) is to allow placing criteria on all (or any) reference properties without knowing them in advance.
Another possibility is to use a subquery:
SELECT * FROM [test:managedElement] WHERE second IN ( SELECT [jcr:uuid] FROM [test:second] WHERE myAttribue = 'hello' )
But again, this is non-standard and actually isn't as flexible as the JOIN since the attributes of "test:second" can't be selected in the outer query. However, there are times when a query with one or more subqueries can very easily achieve the same thing that would require multiple standard JCR queries with quite a bit of Java code in between to process the results and convert into criteria on the subsequent queries.