Count subquery where condition

Databases
1

Hello,

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’

How is it possible to do that?

Thanks for help!

2

not quite, but you’re close :slight_smile:

it will join the tables, group the rows by keyword_id, then count them

3

This is working great, many thanks for your help…

If I understand this correctly… This will join table, count images by keyword_ids and group them together?

4

oh, okay, try this –

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

:slight_smile:

5

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?

6

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)

7

Each keyword can have X images and each image can only have 1 keyword thats because of the structure behind the system.

I dont know how should I properly use count to select all where image_count is 0?

8

may i ask to also to please xplain in words what you want this query to do…

it looks like you are listing all keywords, and the count of how many images each keyword has