sql query help to grab post title, postmeta and featured image

Update

Just noticed that the featured image is actually in the postmeta table so I modified my code to

<?php

 $querystr = "
    SELECT $wpdb->posts.id, $wpdb->posts.post_title, $wpdb->postmeta.meta_value, $wpdb->postmeta._wp_attached_file
    FROM $wpdb->posts, $wpdb->postmeta
    WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
    AND $wpdb->posts.post_status = 'publish'
    AND $wpdb->posts.post_date < NOW()
	AND $wpdb->postmeta.meta_value = 'scheme.php'
    ORDER BY $wpdb->posts.post_date DESC
 ";

 $theposts = $wpdb->get_results($querystr);

 foreach ($theposts as $pageresult) {

    echo '<p><img src=' .$pageresult->_wp_attached_file. ' class=alignleft />' .$pageresult->post_title. '</p>';

}

 ?>

But this returns nothing. Looking more into the database, one post can have multiple entries in postmeta, in my case I need to pull out two rows from post meta (along with some data from posts) but it is stopping after it finds the first.

How can I modify my code to get the feaqtured image out?

Thanks

Oh I see what is wrong with the last posts sql, I am calling wp_postmeta.meta_value twice.

How do I call it so that it matches both i.e.

for post_id = 10

wp_postmeta._wp_page_template = ‘scheme.php’

AND

wp_postmeta._wp_attached_file = not empty

Trying a different tact, instead of going directly into the database poking around the wordpress knowledgebase I found some fucstions that should do what I am after.

I am almost there but for two things

1. It is printing the correct number of results but all of the titles are the title of the page I am currently on when testing the code. How do I make it pull the title from the row in the loop?

2. It is bringing in everything due to the first line requesting everything of status ‘publish’. I also need to limit the output based upon the meta_key of ‘ _wp_page_template’ being set to ‘scheme.php’

<?php $pages = get_pages(array('post_status' => 'publish')); ?>

<?php foreach($pages as $page) { ?>

<h1><?php the_post_thumbnail(); ?><?php the_title(); ?></h1>

<p><?php echo get_post_meta($page->ID, 'schemeprice', true) ?></p>

 <?php }?>

I used query_posts() to achieve what I was after, you can pass all sorts of arguments to it making it very powerful to generate very custom queries