MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query that allows for missing rows
Posted by: Eric Kinney
Date: April 16, 2011 11:04PM

Thanks Rick,

sorry in advance for the larger code snippet. The earlier one I had pared down to get to the heart of the question. This is the code I am actually using, and I used LEFT JOIN in the select that was trying to get Feedback_Occupation:

	$set1=$wpdb->get_results ("
		(SELECT pm4.meta_value as Feedback_Success,Feedback_Occupation,Feedback_Location,Feedback_Product,Feedback_Initials, object_id , rate, rtitle, excerpt from 
			(SELECT pm3.meta_value as Feedback_Occupation,Feedback_Location,Feedback_Product,Feedback_Initials, object_id , rate, rtitle, excerpt from
				(SELECT pm2.meta_value as Feedback_Location,Feedback_Product,Feedback_Initials, object_id , rate, rtitle, excerpt from
					(SELECT pm1.meta_value as Feedback_Product,Feedback_Initials, object_id , rate, rtitle, excerpt from  
						(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')
					tb1 INNER JOIN wp_postmeta pm1 ON pm1.post_id = tb1.object_id WHERE pm1.meta_key = 'Feedback_Product')
				tb2 INNER JOIN wp_postmeta pm2 ON pm2.post_id = tb2.object_id WHERE pm2.meta_key = 'Feedback_Location')
			tb3 LEFT JOIN wp_postmeta pm3 ON pm3.post_id = tb3.object_id WHERE (pm3.meta_key = 'Feedback_Occupation'))
		tb4 INNER JOIN wp_postmeta pm4 ON pm4.post_id = tb4.object_id WHERE pm4.meta_key = 'Feedback_Success')
	");

The above query will not return any feedback(results row) that does not have a row in postmeta for Feedback_Occupation. I only changed the second to the outermost INNER JOIN to a LEFT JOIN

Options: ReplyQuote


Subject
Views
Written By
Posted
3920
April 15, 2011 10:49AM
Re: Query that allows for missing rows
1208
April 16, 2011 11:04PM


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.