MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query that allows for missing rows
Posted by: Eric Kinney
Date: April 22, 2011 02:40PM

This is what worked:
SELECT Feedback_Success,Feedback_Occupation,Feedback_Location,Feedback_Product,Feedback_Initials, object_id , rate, rtitle, excerpt from 
			(SELECT object_id,
				wposts.post_title as rtitle, 
				wposts.post_excerpt as excerpt,
				SUM(IF(meta.meta_key = 'Feedback_Our_Rating',meta.meta_value,null)) AS rate,
				GROUP_CONCAT(IF(meta.meta_key = 'Feedback_Initials',meta.meta_value,null)) AS Feedback_Initials,
				GROUP_CONCAT(IF(meta.meta_key = 'Feedback_Product',meta.meta_value,null)) AS Feedback_Product,
				GROUP_CONCAT(IF(meta.meta_key = 'Feedback_Location',meta.meta_value,null)) AS Feedback_Location,
				GROUP_CONCAT(IF(meta.meta_key = 'Feedback_Occupation',meta.meta_value,null)) AS Feedback_Occupation,
				SUM(IF(meta.meta_key = 'Feedback_Success',meta.meta_value,null)) AS Feedback_Success
				FROM wp_term_relationships 
				INNER JOIN wp_posts wposts ON wposts.ID = object_id 
				INNER JOIN wp_postmeta meta ON meta.post_id = object_id 
				WHERE term_taxonomy_id = '7771934' 
				AND wposts.post_type = 'reviews' 
				AND wposts.post_status = 'publish' 
				GROUP BY object_id,term_taxonomy_id)
		tb4 GROUP BY object_id ORDER BY rate DESC

the only reason I had to nest two queries was to get the second GROUP BY, since a comma delimeted list of fields to group by is interpreted as an or, and allows duplicate object_id's.

GROUP_CONCAT worked just like SUM for strings, when all rows are null except the one you're after, and both SUM and GROUP_CONCAT return null if there is no match, by using the IF statement.

There are doubtless other ways to do this, but this worked at least.

Options: ReplyQuote

Written By
April 15, 2011 10:49AM
Re: Query that allows for missing rows
April 22, 2011 02:40PM

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.