Prepare WPDB with meta key and meta value

Question

I'm trying to prepare WPDB by post type, meta key, and meta value

global $post;
global $wpdb;
$rid = absint($_POST["rid"]); // number
$last_id = absint($_POST["lastID"]); // post ID
$query = $wpdb->prepare(
    "
        SELECT ID
        FROM $wpdb->posts
        WHERE ID > %d
        AND wp_posts.post_type = 'room'
        AND wp_postmeta.meta_key = 'rid'
        AND wp_postmeta.meta_value = %s
        ORDER BY wp_posts.ID DESC
        LIMIT 0, 1", $last_id, $rid);
$results = $wpdb->get_results( $query );
foreach ( $results as $row ) {
    echo $row->ID;
}
die();

All I want to do is get the last ID that fits the criteria

Answer 1

If you run this query manually, you should get a response like

(1054, "Unknown column 'wp_postmeta.meta_key' in 'where clause'")

Long story short, wp_postmeta.meta_key is not a valid column of wp_posts. You need to JOIN the postmeta table to be able to use its columns. (There are many resources out there that explain JOIN, one would be this answer.)

$query = $wpdb->prepare(
    "
        SELECT p.ID
        FROM {$wpdb->posts} AS p
        INNER JOIN {$wpdb->postmeta} AS pm
            ON p.ID = pm.post_id AND pm.meta_key = %s
        WHERE p.ID > %d
        AND p.post_type = %s
        AND pm.meta_value = %s
        ORDER BY p.ID DESC
        LIMIT 0, 1
    ",
    'rid',
    $last_id,
    'room',
    $rid
);