Left Outer Join query results in an error

Question

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?

in all the RDBMS system (except MySQL), you can select only those columns without an aggregate function which are in `group by` list. — Deep, Nov 28 '14 at 13:05
Your query has no `WHERE`, yet your error message does. Are you sure this is the right query and the right error message? — Gordon Linoff, Nov 28 '14 at 13:10
Deepak's right. path.ePath and doc.Filenumer must be in an aggregate function or in the GROUP BY clause. — Verdant, Nov 28 '14 at 13:13
I should have mentioned the RDBMS is OpenBase and I'm using their ObjC classes. — Jim Geldermann, Nov 28 '14 at 13:14
@nhgrif I removed the tag, now waiting for the edit to be accepted. — AStopher, Nov 28 '14 at 13:14
Replaced ON with WHERE and all is good. Thanks to all for the nudge. — Jim Geldermann, Nov 28 '14 at 13:21
@JimGeldermann I answered your question, please review the answer. — AStopher, Nov 28 '14 at 16:43

AStopher · Accepted Answer · 2014-11-28T16:57:21.813

1

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

edited Nov 28 '14 at 16:57

answered Nov 28 '14 at 16:43

AStopher

4,207
11
50
75

@Allan While I agree with you (and updated my answer), I disagree with the upvote you just accumulated- your comment's no longer valid, please remove it. – AStopher Nov 29 '14 at 14:53
As an addendum, Openbase's requirement that `where` be used instead of `on` is a deviation from the SQL standard. This syntax would be invalid in most RDBMS. – Allan Nov 30 '14 at 05:06

Nightmaresux · Answer 2 · 2014-11-28T13:50:32.777

0

You have to group by all columns that you have in your select except the ones where you are using aggregation functions. So it should be like :

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.ePath, path._rowid, doc.Filenumber

For better understanding of why you have to group by all columns in the select list: imagine that you have column ID with vaules 1,1 and Name with David,John . And you will type

select ID, Name from table group by ID

Now you wil group by ID = 1 , so you will get one row with value ID 1 and what value will be in column NAME ? SQL cant just random pick one of the names. So you have to group by both of them

select ID, Name from table group by ID,Name

And the result will be in this case:

ID NAME 
1   David 
1   John

edited Nov 28 '14 at 13:50

answered Nov 28 '14 at 13:35

Nightmaresux

538
3
12

Must be the classes I'm using, the ON throws the error. – Jim Geldermann Nov 28 '14 at 14:07
1

This attempts to provide an answer to the question, but does not actually provide the correct solution. – AStopher Nov 28 '14 at 16:45
Thats why its not accepted as an aswer as you can see... Dont see anything wrong with that. Thank you ! – Nightmaresux Nov 30 '14 at 11:39

Left Outer Join query results in an error

2 Answers2