Get Post ID with POST METAs in single MySQL query

Question

From POSTS->ID, I want the first 12 IDs with post_type = 'product' And with that ID association, I want to fetch all POST META.

I am using this MySQL query, but it is not working:

SELECT posts.ID, postmeta.meta_key, postmeta.meta_value FROM `wp_u8gwgg_posts` as posts

INNER JOIN wp_u8gwgg_postmeta as postmeta on posts.ID = postmeta.post_id

WHERE 

posts.`post_type` = 'product' AND 
posts.post_status = 'publish'

ORDER BY posts.ID

It is giving me redundant post_id in data.:

Post ID | Meta Key       | Meta Value
++++++++++++++++++++++++++++++++++++++
12      | _sku           | 18945236
12      | _price         | 1569.36
12      | _regular_price | 1496.20

I want result as:

Post ID | Meta Key                     | Meta Value
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
12      | _sku, _price, _regular_price | 18945236, 1569.36, 1496.20

If it is possible, do let me the solution.

OR

IF any better approach to this is also welcome.

Thanks

Answer 1

SELECT posts.ID, 
       GROUP_CONCAT(postmeta.meta_key ORDER BY posts.ID) meta_keys, 
       GROUP_CONCAT(postmeta.meta_value ORDER BY posts.ID) meta_values 
FROM wp_u8gwgg_posts as posts
INNER JOIN wp_u8gwgg_postmeta as postmeta on posts.ID = postmeta.post_id
WHERE posts.post_type = 'product' 
  AND posts.post_status = 'publish'
GROUP BY posts.ID
ORDER BY posts.ID