Viewing Computed Value in Database MySQL

Question

 SELECT FirstName, DateOfBirth, CURDATE(),
 (YEAR(CURDATE())-YEAR(DateOfBirth))
 - (RIGHT(CURDATE(),5)<RIGHT(DateOfBirth,5))
 AS age
 FROM basicinformation;

this is my query but what I want is after getting the age I must only view those that are above 18 years old. but if I add where age is greater than or equal to 18. I get query error. This might be because age is "as" (a tempory fieldname) and not really a field name. I hope someone could help me because i will use this for a automatic checking of age if they are already ready to vote or not.

http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error — Gustek, Feb 09 '13 at 07:40

score 0 · Accepted Answer · answered Feb 09 '13 at 07:47

0

just add having age >=18.

SELECT FirstName, DateOfBirth, CURDATE(),
 (YEAR(CURDATE())-YEAR(DateOfBirth))
 - (RIGHT(CURDATE(),5)<RIGHT(DateOfBirth,5))
 AS age
 FROM basicinformation
HAVING age>=18;

answered Feb 09 '13 at 07:47

revoua

2,044
1
21
28

I would not recommend using having in this situation if Your table is going to be big. This query will be fetching and calculating age for all records always, each time You call it. Instead it would be better to have extra column, recalculate its value everyday at midnight and use where with index. Unless You are going to have just few thousands of users it will be fine like this but otherwise...think about it. – Gustek Feb 09 '13 at 08:08

Viewing Computed Value in Database MySQL

1 Answers1