Using Count of images in the where clause of MYSQL

Im trying to create a MYSQL query to select the latest 10 advert records where the number of images is greater than 0. The problem is im getting the error "Unknown column 'num_photos' in 'where clause'" because the where statement is executed before the count. How would I rewrite this mysql query to get around this?

    
SELECT开发者_如何学Go adverts.*,
( SELECT advert_images.imagethumbpath FROM advert_images WHERE advert_images.advert_id = adverts.advert_id  ORDER BY image_id ASC LIMIT 1 ) as imagethumbpath,
( SELECT count(advert_images.advert_id) from advert_images WHERE advert_images.advert_id = adverts.advert_id ) AS num_photos
FROM adverts
WHERE num_photos > 0
ORDER BY date_updated DESC 
LIMIT 10

You can do this using HAVING instead of WHERE:

SELECT adverts.*,
( SELECT advert_images.imagethumbpath FROM advert_images WHERE advert_images.advert_id = adverts.advert_id  ORDER BY image_id ASC LIMIT 1 ) as imagethumbpath,
( SELECT count(advert_images.advert_id) from advert_images WHERE advert_images.advert_id = adverts.advert_id ) AS num_photos
FROM adverts
HAVING num_photos > 0
ORDER BY date_updated DESC 
LIMIT 10

You have to use having clause instead of where

having num_photos > 0

If the majority of your adverts have images, this should be pretty fast.

select a.advert_id
      ,a.date_updated
      ,b.imagethumbpath
  from adverts        a
  join advert_images  b on(a.advert_id = b.advert_id)
 where b.image_id = 
        (select min(c.image_id)
           from advert_image c
          where c.advert_id = b.advert_id)
 order 
    by date_updated desc
 limit 10;  

You should index adverts(date_updated) and advert_images(advert_id, image_id).

Let me know how this works out.