I am trying to do a manual query for WordPress postmeta table which would combine two types of postmeta into one with multiple columns by post_id.

This is the structure of table:

meta_id | post_id | meta_key                | meta_value
1       | 100     | _wp_attachment_metadata | a:6:{s:5:"width"}
2       | 100     | _thumbnail_id           | 23

This is the result I am trying to achieve:

post_id | _wp_attachment_metadata | _thumbnail_id
100     | a:6:{s:5:"width"}       | 23

1 Answer 1

SELECT t1.post_id, t1.meta_value, t2.meta_value
FROM postmeta t1, postmeta t2
WHERE t1.post_id = t2.post_id
  AND t1.meta_key = '_wp_attachment_metadata'
  AND t2.meta_key = '_thumbnail_id'


SELECT post_id, 
       MAX(CASE WHEN meta_key = '_wp_attachment_metadata' 
                THEN meta_value END), 
       MAX(CASE WHEN meta_key = '_thumbnail_id' 
                THEN meta_value END)
FROM postmeta 
GROUP BY post_id

PS. First query skips post_id, if it have no one (any) of meta-keys, second query gives NULL for according output field in such case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.