<?php
global $wpdb;
$query = "
SELECT *
FROM {$wpdb->prefix}posts
INNER JOIN {$wpdb->prefix}postmeta m1
  ON ( {$wpdb->prefix}posts.ID = m1.post_id )
INNER JOIN {$wpdb->prefix}postmeta m2
  ON ( {$wpdb->prefix}posts.ID = m2.post_id )
WHERE
{$wpdb->prefix}posts.post_type = 'post'
AND {$wpdb->prefix}posts.post_status = 'publish'
AND ( m1.meta_key = 'date' AND m1.meta_value > '2010-12-05 00:00:00' )
AND ( m1.meta_key = 'date' AND m1.meta_value < '2010-12-12 00:00:00' )
AND ( m2.meta_key = 'some_other_meta_value' AND m2.meta_value != '' )
GROUP BY {$wpdb->prefix}posts.ID
ORDER BY {$wpdb->prefix}posts.post_date
DESC;
";
?>