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
