where clausule on field defined by sub-query

Posted by stUrb on Stack Overflow See other posts from Stack Overflow or by stUrb
Published on 2012-07-11T09:02:44Z Indexed on 2012/07/11 9:15 UTC
Read the original article Hit count: 124

Filed under:

I have this query which displays some properties and count the number of references to it from an other table:

SELECT 
       p.id,p.propName
       (
         SELECT COUNT(*) FROM propLoc WHERE propLoc.propID = p.id
       ) AS number
FROM property as p 
WHERE p.category != 'natural'

This generates a good table with all the information I want to filter:

id | propName | number
3  | Name 1   | 3
4  | Name 2   | 1
5  | Name 3   | 0
6  | Name 4   | 10
etc etc

I now want to filter out the properties with number <= 0 So I tried to add an AND number > 0 But it reacts with Unknown column 'number' in 'where clause' apparently you can't filter on a name specified by a subquery?

How can I achieve my goal?

© Stack Overflow or respective owner

Related posts about mysql