Search code examples
sqlouter-joinopenbase

Left Outer Join query results in an error

This query throws ERROR - [position 0, near 'WHERE' in 'Select path.ePath, path._'] COMMA expected but not found in GROUP BY.

This is my query:

Select path.ePath, path._rowid, doc.Filenumber, COUNT(doc.ePathUID) AS children
from docPath path LEFT OUTER JOIN
     docMeta doc
     ON doc.ePathUID = path._rowid
GROUP BY path._rowid

I'm expecting to count every child of the relationship, but the query isn't working. How can I make the query work?

Solution

  • You are using the ON statement in the wrong context, it should be WHERE instead:

    Select path.ePath, path._rowid, doc.Filenumber, COUNT(doc.ePathUID) AS children
from docPath path LEFT OUTER JOIN
     docMeta doc
     WHERE doc.ePathUID = path._rowid
GROUP BY path._rowid