SQL query on multiple custom fields

Hi,

I am trying to set up a custom SQL query that would return posts based on parameters of TWO custom fields.

Right now, I can get it to work only with one field, with this query :

$querystr = "
SELECT wposts.*
FROM $wpdb->posts wposts
	LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
	LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
	LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE
	(wpostmeta.meta_key = 'City' AND (wpostmeta.meta_value = '$city1' OR wpostmeta.meta_value = '$city2'))
	AND $wpdb->term_taxonomy.taxonomy = 'category'
	AND $wpdb->term_taxonomy.term_id IN(3,4)
ORDER BY wpostmeta.meta_value ASC
";

As you can see, it queries posts belonging to category 3 or 4, with the custom field “City” being equal to $city1 or $city2.

Now, I’d like to add a second custom field named “Price” to the query, how would I do that?

I tried the following, which doesn’t seem to work :

$querystr = "
SELECT wposts.*
FROM $wpdb->posts wposts
	LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
	LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
	LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE
	(wpostmeta.meta_key = 'City' AND (wpostmeta.meta_value = '$city1' OR wpostmeta.meta_value = '$city2'))
	AND
	(wpostmeta.meta_key = 'Price' AND wpostmeta.meta_value BETWEEN = '2000' AND '10000')
	AND $wpdb->term_taxonomy.taxonomy = 'category'
	AND $wpdb->term_taxonomy.term_id IN(3,4)
ORDER BY wpostmeta.meta_value ASC
LIMIT 50
";

Any advice ?