[BNM] fancy sql union?

[Ali]

Hi

I've got to look at the `added` datetime fields of 3 mysql4.1 tables with differing structures (but the relevant fieldnames are the same in all 3 tables), pick the the 3 most recent from all tables and return only dates that fall within the last 30 days. So if there were only 2 dates within last 30 days the results would not be the 3 most recent but the 2 most recent.

Whilst I know I can use PHP to sort the resulting array of 9 results from the sql below by `added` and return only the first 3 (or less if only 1 or 2  records) does anyone know a way to achieve this using sql alone?

Here's my current sql

 $sql="
(SELECT module_articles.uid,module_articles.title,module_articles.added,'module_articles' AS tablename 
 FROM module_articles 
 WHERE module_articles.added >= DATE_SUB(CURDATE(),INTERVAL 30 DAY) 
ORDER BY module_articles.added DESC LIMIT 3)
 UNION
 (SELECT case_studies.uid,case_studies.title,case_studies.added,'case_studies' AS tablename 
 FROM case_studies 
 WHERE case_studies.added >= DATE_SUB(CURDATE(),INTERVAL 30 DAY) 
ORDER BY case_studies.added DESC LIMIT 3)
 UNION
 (SELECT kb_articles.uid,kb_articles.title,kb_articles.added,'kb_articles' AS tablename 
 FROM kb_articles 
 WHERE kb_articles.added >= DATE_SUB(CURDATE(),INTERVAL 30 DAY) 
ORDER BY kb_articles.added DESC LIMIT 3)
 ";

I would be moderately happy to learn I can't!

Ali