Search code examples
mysqlselectgroup-bygroup-concat

MySQL selecting only certain values from column without excluding rows

I was wondering if there was a way to select only certain values in a column for use in a GROUP_CONCAT without excluding the non-matching columns from the SELECT.

Here's the specific scenario: for my WordPress site, I have wp_22_posts and wp_22_postmeta tables with a one-to-many relationship between the posts and the meta. I would like to group the joined results by wp_22_posts.ID and select a semicolon-separated list of wp_22_postmeta.meta_key and wp_22_postmeta.meta_value for each group. I only want to grab the meta key and value if the key matches "authorid" or "isbn"; However, I do not want to exclude the rows without an "authorid" or "isbn" - I would rather just return null or something like that for the non-matching rows.

Below is the query that I am currently using, but as you can see, it is pulling in all of the metadata:

SELECT posts.post_title, MAX(posts.post_date_gmt), posts.post_content, posts.guid AS url, posts.comment_count,
    GROUP_CONCAT(postmeta.meta_key ORDER BY postmeta.meta_value DESC SEPARATOR ';') AS meta_key,
    GROUP_CONCAT(postmeta.meta_value ORDER BY postmeta.meta_value DESC SEPARATOR ';') AS meta_value
FROM wp_22_posts AS posts
JOIN wp_22_postmeta AS postmeta ON (posts.ID = postmeta.post_id)
WHERE posts.post_status = 'publish'
GROUP BY posts.ID
Solution

  • I believe you can use CASE instead of column name in GROUP_CONCAT :

    GROUP_CONCAT(CASE WHEN postmeta.meta_key='authorid' OR postmeta.meta_key='isbn'
 THEN postmeta.meta_key END, .....

    Sorry, I cannot test it at the moment, but it should work