Why is left outer join failing here for me?

Question

I see nothing wrong here, but why does this give me

/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left outer join votes on items.id = votes.parent and votes.userid = 1 group by i' at line 2 */


select maxVotes, sum(case when coalesce(votes.id, 0) then 1 else 0 end) votesCast from items where type = 'marker'
left outer join votes on items.id = votes.parent and votes.userid = 1 group by items.id;

I'm doing this using mySql.

Answer 1

Change to

select maxVotes, 
       sum(case when coalesce(votes.id, 0) then 1 else 0 end) votesCast 
  from items left outer join votes -- <-- your JOIN clause should go here
    on items.id = votes.parent 
   and votes.userid = 1 
 where type = 'marker' -- <-- and WHERE here
group by items.id;

On a side note: even though MySql allows to specify a field (in your case maxVotes) in SELECT that is not a part of GROUP BY it's a not a good thing to do. You need to apply an aggregate function to that field (MAX, MIN...). There is no way to tell which value of maxVotes to grab when you do GROUP BY items.id.