MySQL Forums
Forum List  »  Optimizer & Parser

Query that allows for missing rows
Posted by: Eric Kinney
Date: April 15, 2011 10:49AM

I have a row I match in table A(posts), and then collect data from associated rows in table B(postmeta)

I want this to return the collected result row whether or not there is a row in wp_postmeta in which meta_key = "Feedback_Initials"

so I am trying to do that with inner join and nested selects

the trouble is that if a meta value is missing (no row exists for it in postmeta, for example there is no Feedback_initials row for that ID) then I would still like to get the row, and just have the value for feedback initials empty.

As it is right now it omits that row and ID from the results entirely if there was no Feedback_Initials. I've simplified this to only include Feedback_Initials and Feedback_our_Rating, but there are several other items with rows in table B that I'd want to collect.

$set1=$wpdb->get_results ("
(SELECT pm.meta_value as Feedback_Initials, object_id , rate, rtitle, excerpt from
(SELECT object_id, meta.meta_value as rate, wposts.post_title as rtitle, wposts.post_excerpt as excerpt FROM $wpdb->term_relationships INNER JOIN $wpdb->posts wposts ON wposts.ID = object_id INNER JOIN $wpdb->postmeta meta ON meta.post_id = object_id WHERE term_taxonomy_id = '".$pcvalue."' AND wposts.post_type = 'reviews' AND wposts.post_status = 'publish' AND meta.meta_key = 'Feedback_Our_Rating' ORDER BY CAST(meta.meta_value as unsigned integer) DESC)
tb INNER JOIN wp_postmeta pm ON pm.post_id = tb.object_id WHERE pm.meta_key = 'Feedback_Initials')
");


Is there a way I can modify my query to still return the row if there is no row in meta_key for Feedback_Initials?

The only way I know to do it is to do a second query that does not check for Feedback_Initials, and then merge the two results removing duplicates. Helpful souls at stack overflow suggested left join rather than inner join, but that did not seem to affect the outcome, and I'm stalemated. Any ideas?

Options: ReplyQuote


Subject
Views
Written By
Posted
Query that allows for missing rows
3921
April 15, 2011 10:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.