I'm having trouble doing an otherwise SQL valid self-join query on documentdb.
So the following query works:
SELECT * FROM c AS c1 WHERE c1.obj="car"
But this simple self join query does not: SELECT c1.url FROM c AS c1 JOIN c AS c2 WHERE c1.obj="car" AND c2.obj="person" AND c1.url = c2.url, with the error, Identifier 'c' could not be resolved.
It seems that documendb supports self-joins within the document, but I'm asking on the collection level.
I looked at the official syntax doc and understand that the collection name is basically inferred; I tried changing c to explicitly my collection name and root but neither worked.
Am I missing something obvious? Thanks!