Subquery question
Posted: Fri Jan 04, 2008 2:12 pm
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:
Table posts:
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:
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.
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_valueCode: Select all
post_id, post_content, ... , ...- 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'
)";