Retrieving custom fields with $wpdb->get_results

Question

I'm trying to use $wpdb->get_results to retrieve an array of all posts including custom fields. I can retrieve the meta_key and meta_value columns like this:

[meta_key] => state [meta_value] => california

but I'm trying to list them in the object like this:

[meta_key] => meta_value [state] => california [city] => san francisco 

The Query:

global $wpdb;
$query = "
SELECT ID, post_date, post_title, post_content, guid, meta_key, meta_value
FROM wp_posts INNER JOIN wp_postmeta
ON (wp_posts.ID = wp_postmeta.post_id)
";

$results = $wpdb->get_results($query);

foreach($results as $result) {
    print_r($result);
}

Is it possible to use an alias and/or subquery to achieve this?

SELECT ID, post_date, post_title, post_content, guid, meta_value AS (subquery here??)

Answer 1

Note, before going further: Take care about portability and security:

function wpse50056_get_post_meta()
{
    global $wpdb;

    $query = $wpdb->prepare( "
        SELECT 
            ID, 
            post_date, 
            post_title, 
            post_content, 
            meta_key, 
            meta_value
        FROM %s 
            INNER JOIN %s 
        ON (  %s.ID =  %s)
    ",
    $wpdb->posts,
    $wpdb->postmeta,
    $wpdb->posts,
    "{$wpdb->postmeta}.post_id" );

    $results = $wpdb->get_results( $query );

    foreach( $results as $result )
    {
        echo '<pre>'; print_r( $result ); echo '</pre>';
    }
}
add_action( 'shutdown', 'wpse50056_get_post_meta' );