JCR-SQL2 Select all nodes that have child node with specific property value

Question

I have the following node structure in the JCR

/testNode (mgnl:content)
   |--- child (mgnl:contentNode)
          |--- property : value

How can I select all nodes of type mgnl:content that have property child.property = 'value' using JCR-SQL2? An xpath example would also be greatly appreciated as well as explanation of them.

Also if I want to change the property=value equation to a LIKE operation how would I do that?