Is there any option to get child node in SQL query

Hi,
I guess that with SQL2 you mean JCR-SQL2? And there, as you said, you have the option to use the following code with the ISDESCENDANTNODE function:
 

qm.createQuery("SELECT * FROM [nt:unstructured] AS node WHERE ISDESCENDANTNODE([" + root.getPath() + "]) and CONTAINS(node.*, '" + searchTerm + "')", Query.JCR_SQL2);

Im not sure exactly what you mean with SQL Query ?

If you do not want to use JCR-SQL2 there is another option to use JCR-JQOM (JCR Java Query Object Model). There you have the equivalent to ISDESCENDANTNODE() which is called "descendantNode". It can be used in the same way with this code example:
 

.... Constraint myConstraint = qf.fullTextSearch(MY_SELECTOR_NAME, null, qf.literal(vf.createValue(queryTerm))); myConstraint = qf.and(myConstraint, qf.descendantNode(MY_SELECTOR_NAME, queryRoot.getPath())); QueryObjectModel query = qf.createQuery(selector, constraint, null, null); .....

Then you also have the possibility to use the XPATH queries which have the equivalent expression "child::node()" which can be used to retrieve the child nodes of a ceratin node like this:

 

/jcr:root/content/mysite/en/news/2013/child::node()

Hope it's some kind of help!
Regards Johan