I’d like to set WHERE condition on count subquery:
SELECT `keywords`.*, (SELECT COUNT(*) AS count FROM (`images`) LEFT OUTER JOIN `keywords` `keywords_subquery` ON `keywords_subquery`.`id` = `images`.`keyword_id` WHERE `keywords_subquery`.id = `keywords`.`id`) AS image_count FROM (`keywords`)
If I add WHERE image_count > 0 it will give me error: Unknown column ‘image_count’ in ‘where clause’
SELECT keywords.*
, COUNT(images.keyword_id) AS image_count
FROM keywords
LEFT OUTER
JOIN images
ON images.keyword_id = keywords.id
GROUP
BY keywords.id
HAVING image_count < 2
in the subquery you are using COUNT(*) at the same time as LEFT OUTER JOIN – i wonder if you realize that whenever you have an image without any keywords at all, the count will still be 1
also, am i right in thinking that your images can have only one keyword? if so, why?
I want this query to select all keywords which have less than 2 images (related image records).
In other words: I want to select all where image_count is less than 2.
I can only think of sql query which would return keywords with 0 related images by doing:
SELECT keywords WHERE ID NOT IN(get image ids subquery)