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'
)";