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
mysql
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