Home / Questions / 418067
Accepted
Jose Dario Correa
Asked: 2022-01-03 13:57:22 +0800 CST 2022-01-03 13:57:22 +0800 CST

Group MySQL query

  • 772

The following table shows the result I get from a query:

SELECT pm.post_id, pm.meta_value
FROM posts p
LEFT JOIN terms t ON p.post_title = t.name
LEFT JOIN term_relationships tr ON t.term_id = tr.term_taxonomy_id
LEFT JOIN postmeta pm ON tr.object_id = pm.post_id
WHERE p.post_status = 'publish' AND p.post_type = 'lrooms' AND p.post_author = '1' AND pm.meta_key IN ('_appointment_timestamp', '_appointment_timeslot', '_appointment_guest_name', '_appointment_guest_surname', '_appointment_guest_email')
ORDER BY pm.meta_id DESC
post_id meta_value
9247 1730-1800
9247 1609608600
9247 [email protected]
9247 Belt
9247 jose dario
9245 1130-1200
9245 1609414200
9245 [email protected]
9245 Pinedo
9245 Andrew

I would like to group/concatenate the values ​​of the meta_value column as follows:

post_id meta_value
9247 1730-1800;1609608600;[email protected];Correa;Jose Dario
9245 1130-1200;1609414200;[email protected];Pinedo;Andres

How can I make this possible?

mysql

1 Answers

  1. Best Answer

    Problem solved!

    Taking into account @RobertoLeOr 's answer, I adjusted my query as follows:

    SELECT pm.post_id, GROUP_CONCAT(pm.meta_value ORDER BY pm.meta_key DESC SEPARATOR ';') AS 'meta_value'
FROM posts p
LEFT JOIN terms t ON p.post_title = t.name
LEFT JOIN term_relationships tr ON t.term_id = tr.term_taxonomy_id
LEFT JOIN postmeta pm ON tr.object_id = pm.post_id
WHERE p.post_status = 'publish' AND p.post_type = 'lrooms' AND p.post_author = '1' AND pm.meta_key IN ('_appointment_timestamp', '_appointment_timeslot', '_appointment_guest_name', '_appointment_guest_surname', '_appointment_guest_email')
ORDER BY pm.meta_id DESC

    The setting in particular was the following, right in the select of my query:

    GROUP_CONCAT(pm.meta_value ORDER BY pm.meta_key DESC SEPARATOR ';') AS 'meta_value'

    And I was able to get the desired result.

    • 0