Subquery question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Subquery question

Post 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.
devendra-m
Forum Contributor
Posts: 111
Joined: Wed Sep 12, 2007 3:16 am

Post by devendra-m »

I think inline view will be better than subquery.
Post Reply