JCR query all nodes under a parent node

Question

I am trying to list, sort and paginate all folders and files under a parent node.This is my query selecting only folders and it gives me the expected result:

SELECT childFolder.[jcr:name], childFolder.[jcr:uuid] FROM [nt:base] AS parent LEFT OUTER JOIN [nt:folder] AS childFolder ON ISCHILDNODE(childFolder, parent) WHERE parent.[jcr:uuid] = 'a54ca11b-99f8-42cf-8348-8c0f3bc4f008' ORDER BY parent.[jcr:primaryType] DESC

Then I add another join for the files but it doesn't return any results:

LEFT OUTER JOIN [nt:file] AS childFile ON ISCHILDNODE(childFile, parent)

Final query:

SELECT childFolder.[jcr:uuid] FROM [nt:base] AS parent LEFT OUTER JOIN [nt:folder] AS childFolder ON ISCHILDNODE(childFolder, parent) WHERE parent.[jcr:uuid] = 'a54ca11b-99f8-42cf-8348-8c0f3bc4f008' ORDER BY parent.[jcr:primaryType] DESC

1.Is there any way to get folders and files with a single query? 2.In the first query I try to get [jcr:name] but it gives me null.How can I get the name of the nodes?

Answer 1

This one's working:

SELECT [jcr:primaryType], [jcr:created], [jcr:createdBy], [jcr:path] FROM [nt:file] 
UNION
SELECT [jcr:primaryType], [jcr:created], [jcr:createdBy], [jcr:path] FROM [nt:folder]