selecting rows with a certain age

[dweb]

Hi

 

I have the following query;

 

SELECT m.name,m.gender,m.dob, (SELECT FLOOR( DATEDIFF( 'date('Y-m-d')',m.dob ) / 365.25 )) AS age FROM members m
 

which gets

 

David      Male      1990-08-17       22
John       Male      1978-02-15       35

 

and that all works ok

 

what i want to do, is extend the query to grab rows when the member is between a set age

 

but when i do

 

WHERE age BETWEEN 30 AND 40
 

i get

Unknown column 'age' in 'where clause'
 

any idea why?

 

thanks

[mac_gyver]

this is a chicken and egg age problem. the age value is calculated in the select term for the rows that have been selected. the where term determines which rows to select. the easiest, not fastest, way would be to use a HAVING age BETWEEN 30 AND 40 term. the fastest way would be to put the calculation into the WHERE term.

[dweb]

thank you, works great