Page 1 of 1

Subquery question

Posted: Fri Jan 04, 2008 2:12 pm
by matthijs
I have a - for me - complicated query, which seems to work. But I'd appreciate it if someone with more sql experience would take a look.

For a weblog I have 2 tables: postmeta and posts. Each post can have more meta-keys attached. In my case, some have a meta_key "excerpt" and a meta_key "surname".
Table postmeta:

Code: Select all

meta_id, post_id , meta_key, meta_value
Table posts:

Code: Select all

post_id, post_content, ... , ...
Now what I want to do:
- select all posts where the meta_key "surname" starts with some letter. That letters comes from a search by a user.
- then select the matching data from those posts AND select the matching meta_keys "excerpt".

I cannot do a simple join, because I first need to select the join on the meta key surname, and then select the metakeys excerpt. So I thought a subquery would work:

Code: Select all

		$mainquery = "SELECT $wpdb->posts.*, $wpdb->postmeta.*
						FROM  $wpdb->posts
						INNER JOIN $wpdb->postmeta 
						ON $wpdb->posts.ID = $wpdb->postmeta.post_id
						WHERE $wpdb->postmeta.meta_key='Excerpt'
						AND $wpdb->posts.post_parent = 22
						AND $wpdb->posts.ID IN (
							SELECT post_id 
							FROM $wpdb->postmeta 
							WHERE $wpdb->postmeta.meta_value LIKE '". $safesearchletter ."%' 
							AND $wpdb->postmeta.meta_key='Surname'
							)";	
It seems to give the results I want, but if there's something I miss or if there's a better way to do this, please tell me.

Posted: Wed Jan 09, 2008 5:10 am
by devendra-m
I think inline view will be better than subquery.