OK, really I should have RTFM closer on this one (at least the tutorial
section). http://www.mysql.com/doc/en/Date_calculations.html even has a part
about computing ages. However, I guess my original question remains, which
is, is there a better, cleaner way to do it than this, which computes the
age twice, albeit for different users:
SELECT user_b.* FROM user AS user_a, user AS user_b WHERE
(YEAR(CURDATE())-YEAR(user_a.date_of_birth)) -
(RIGHT(CURDATE(),5)<RIGHT(user_a.date_of_birth,5)) # get the age of user_a
BETWEEN user_b.min_age AND user_b.max_age) AND
(YEAR(CURDATE())-YEAR(user_b.date_of_birth)) -
(RIGHT(CURDATE(),5)<RIGHT(user_b.date_of_birth,5)) #get the age of user_b
BETWEEN user_a.min_age AND user_a.max_age)
/* and for a specific user maybe */ AND user_a.id = 5;
----- Original Message -----
From: "Noah Davis" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, August 30, 2003 4:31 AM
Subject: Computing Age for Select Query
I'm writing a SQL query that returns a set of users based each user's mutual
matching preferences. Picture a dating service, where one user is 35 looking
to meet 25-35 year olds, and another (matching) user is 25 looking to meet
30-35 year olds. The relevant fields in the user table would be:
date_of_birth (date)
min_age (int) -- the min age a match is allowed to be
max_age (int) -- the max age a match is allowed to be
The pseudo SQL I have for this is something like:
SELECT user_b.* FROM user AS user_a, user AS user_b WHERE
(getAge(user_a.date_of_birth) BETWEEN user_b.min_age AND user_b.max_age) AND
(getAge(user_b.date_of_birth) BETWEEN user_a.min_age AND user_a.max_age) /*
and for a specific user maybe */ AND user_a.id = 5;
All well and good, except there's obviously no such "getAge" function, nor,
as I understand it, is there a way in MySQL to define one. What's the
recommended practice for a situation like this? Is there a way I can do this
"function-like" so I don't end up duplicating a lot of strange SQL code
where the getAge function would be in both expressions? I guess I'm less
concerned here about the date/time functions of MySQL (unless there actually
is a getAge function), as I am to figuring out the proper/elegant way to use
them.
Many thanks for any help or gentle shove(s) in the right direction.
Noah
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
