mySQL: Select WHERE causes error - why?

Question

Hi everybody,

I have this query:

SELECT `manufacturers`.*, `languages`.*, COUNT(`products`.`id`) AS productcount
FROM (`manufacturers`)
WHERE `manufacturers`.`flushed` = `0`
JOIN `languages` ON `manufacturers`.`lang` = `languages`.`id`
LEFT OUTER JOIN `products` ON 
`products`.`manuf` =  `manufacturers`.`mid`
GROUP BY manufacturers.id
ORDER BY `languages`.`id` asc, `manufacturers`.`mid` asc;

Without the WHERE row, everything works great, but with it, I get a Error 1064 (Syntax error) thrown in my face. I guess that it has something to do with the actual placement of the WHERE row in the query, so I tried to move it around, but without any luck.

What can I do?

Answer 1

Where goes after the joins, before the group by (joins are considered to be part of the from clause)

Answer 2

Check this:

SELECT `manufacturers`.*, `languages`.*, COUNT(`products`.`id`) AS productcount
FROM (`manufacturers`)
JOIN `languages` ON `manufacturers`.`lang` = `languages`.`id`
LEFT OUTER JOIN `products` ON 
`products`.`manuf` =  `manufacturers`.`mid`
WHERE `manufacturers`.`flushed` = `0             -- where comes after joins 
GROUP BY manufacturers.id
ORDER BY `languages`.`id` asc, `manufacturers`.`mid` asc;