So I'm doing a query that looks like this:


SELECT id FROM (SELECT id,dobdiff FROM
  (SELECT id,DATEDIFF(DATE_SUB(dob, INTERVAL YEAR(dob) YEAR),DATE_SUB(CURDATE(),INTERVAL YEAR(CURDATE()) YEAR)) AS dobdiff FROM dobs) AS ddtab1
WHERE dobdiff >= 0) AS ddtab2 WHERE dobdiff = (SELECT min(dobdiff) FROM (SELECT id,dobdiff FROM
  (SELECT id,DATEDIFF(DATE_SUB(dob, INTERVAL YEAR(dob) YEAR),DATE_SUB(CURDATE(),INTERVAL YEAR(CURDATE()) YEAR)) AS dobdiff FROM dobs) AS ddtab3
WHERE dobdiff >= 0) AS ddtab4)


which all works OK. But as you can see, two subqueries, ddtab1 and ddtab3 are the same, and so ddtab2 and ddtab4 are the same.

So my question is: is there any way to make so that I am using fewer subqueries or so that i can refer to previous subqueries subsequently?
Posted on 2005-05-23 07:16:01 by stormix