COUNT in SELECT QUERY Issue

[johnrb87]

Hi All

 

I have the following QUERY

 

SELECT my.id, my.position, my.date, my.active, (SELECT COUNT(*) FROM `stuff` WHERE user = my.id) AS total_count FROM staff my WHERE my.position != 99 AND total_count = 0 ORDER BY my.id ASC

 

it works great, apart from the part

 

AND total_count = 0

 

which causes an error.

 

Why can it not do this?

 

Thanks very much

[mikosiko]

my.total_count

[johnrb87]

my.total_count

 

I get a

 

#1054 - Unknown column 'my.total_count' in 'where clause'

 

error

[mikosiko]

my bad... if my memory doesn't fail you can't use a subquery alias in a where clause (I couldn't find the specific paragraph in the manual)

 

this is one way to solve it:

SELECT my.id,

            my.position,

            my.date, my.active,

            (SELECT COUNT(*) FROM `stuff` WHERE user = my.id) AS total_count

FROM staff my

WHERE my.position != 99

HAVING total_count = 0

ORDER BY my.id ASC

[johnrb87]

nice 1, thanks mikosiko