Ordering of subquery flowing to main query? [SOLVED]
Posted: Thu Jan 31, 2008 11:11 am
Ok, I have this query using a subquery. The problem is, I want to sort the results by something in the subquery. Something which isn't used in the result of the main query.
You can see that in the last line I have the ORDER BY clause. Running the subquery on it's own gives me the correct results. But since Surname is not used in the results in the mainquery, the end results are ordered on something else (the posts.ID) in this case.
To give a bit of context: there are 2 tables. posts and posts_meta. Each post has a meta_key 'Excerpt' and a meta_key 'Surname'. So what the purpose is of this query is first search the posts_meta for a Surname with a certain letter. This subquery returns a list of postID's. Then the main query returns some data from the posts and the meta data Excerpts belonging to those posts.
Is it somehow possible to use the order of the IN clause to get the same order in the end result?
Or can I rewrite the whole query in such a way that I get the same effect?
I also tried:
Now I get many duplicate results. I should get unique posts.ID. In this case the DISTINCT doesn't 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 = '24'
AND $wpdb->posts.ID IN (
SELECT post_id
FROM $wpdb->postmeta
WHERE $wpdb->postmeta.meta_value LIKE '". $safesearchletter ."%'
AND $wpdb->postmeta.meta_key='Surname'
ORDER BY $wpdb->postmeta.meta_value ASC
)
";
To give a bit of context: there are 2 tables. posts and posts_meta. Each post has a meta_key 'Excerpt' and a meta_key 'Surname'. So what the purpose is of this query is first search the posts_meta for a Surname with a certain letter. This subquery returns a list of postID's. Then the main query returns some data from the posts and the meta data Excerpts belonging to those posts.
Is it somehow possible to use the order of the IN clause to get the same order in the end result?
Or can I rewrite the whole query in such a way that I get the same effect?
I also tried:
Code: Select all
$mainquery = "SELECT DISTINCT $wpdb->posts.ID, $wpdb->postmeta.*
FROM $wpdb->posts
INNER JOIN $wpdb->postmeta
ON $wpdb->posts.ID = $wpdb->postmeta.post_id
WHERE $wpdb->posts.post_parent = '24'
AND $wpdb->posts.ID IN (
SELECT post_id
FROM $wpdb->postmeta
WHERE $wpdb->postmeta.meta_value LIKE '". $safesearchletter ."%'
AND $wpdb->postmeta.meta_key='Surname'
ORDER BY $wpdb->postmeta.meta_value ASC
)
";