CODEWITHSUNDEEP
mysqlsqljoin
jmenezes
jmenezes

Reputation: 1926

Why is left outer join failing here for me?

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.

Upvotes: 3

Views: 199

Answers (3)

peterm
peterm

Reputation: 92785

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.

Upvotes: 2

Ravi
Ravi

Reputation: 31407

Try this...

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

Upvotes: 1

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

Move your JOIN before WHERE clause:

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

Upvotes: 2

Related Questions