Need I say more?

SELECT posts.ID, posts.post_title, meta.meta_value
FROM wp_posts as posts
LEFT JOIN wp_postmeta as meta
ON posts.ID=meta.post_id where
posts.post_type=’page’ and posts.post_status=’publish’ and  meta.meta_key like ‘_wp_page_template’;

While making my own archives page, I needed to display the most recent 3 months posts (by sub-category list, where parent category_id=7). So I needed to smash all the tables together & filter out what I didn’t need. In this case, I needed the category names which had posts in the past 3 months. Easily modifiable to get the post names as well.

global $wpdb;
$categories = $wpdb->get_results("select wt.term_id,wt.name,wtt.description from $wpdb->term_relationships as `wtr`
left join $wpdb->posts wp on wtr.object_id=wp.id
left join $wpdb->term_taxonomy wtt on wtr.term_taxonomy_id=wtt.term_id
left join $wpdb->terms wt on wt.term_id=wtt.term_id
where  parent = '7' and post_date>= DATE_SUB(CURDATE(), INTERVAL 3 month) and post_type='post' and post_status='publish'; ");

foreach($categories as $category) {
print_r( $category);
}
?>