where clausule on field defined by sub-query
- by stUrb
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?
