mySQL query returns 3 columns, I want to get values from one column concatenated into comma separated string

Question

I need to get the values from the meta_value column that have "shipping" in the corresponding meta_key column and concatenate them into a comma separated row.

I'm a bit of a noob to SQL so please be gentle.

My current query is this:

SELECT wp_postmeta.post_ID, wp_postmeta.meta_key, wp_postmeta.meta_value, wp_posts.ID, wp_posts.post_status 
FROM wp_postmeta 
CROSS JOIN wp_posts 
WHERE wp_postmeta.post_ID= wp_posts.ID 
  AND wp_posts.post_status LIKE 'wc-processing' 
  AND (wp_postmeta.meta_key LIKE '%shipping%' OR wp_postmeta.meta_key LIKE '_order_total')

You don't want a cross join (which is also called a cartesian product). That gives you the number of records in the first table X the number of records in the 2nd table. You probably want an inner join, which gives you matching records. FYI, the remaining joins are left, right and full. There's a nice Venn diagram out there: https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ — kermit, Nov 12 '18 at 01:36
What will be expected output, as per your given sample data ? — Madhur Bhaiya, Nov 12 '18 at 07:41

score 0 · Answer 1 · answered Nov 12 '18 at 01:53

So, here's your query (note how I write it - I find that good formatting of SQL helps to read it):

SELECT  
    wp_postmeta.post_ID, 
    wp_postmeta.meta_key, 
    wp_postmeta.meta_value, 
    wp_posts.ID, 
    wp_posts.post_status 
FROM 
    wp_postmeta 
CROSS JOIN 
    wp_posts 
WHERE 
    wp_postmeta.post_ID = wp_posts.ID 
    AND wp_posts.post_status LIKE 'wc-processing' 
    AND (wp_postmeta.meta_key LIKE '%shipping%' OR wp_postmeta.meta_key LIKE '_order_total')

Here's what I think you want (you use the MySQL concat function):

Select
   concat(
        wp_postmeta.meta_key, ',',
        wp_postmeta.meta_value
    ) as formatted_row
FROM 
    wp_postmeta 
INNER JOIN 
    wp_posts 
ON
    wp_postmeta.post_ID = wp_posts.ID 
WHERE 
    wp_posts.post_status LIKE 'wc-processing' 
    AND (
           wp_postmeta.meta_key LIKE '%shipping%' 
           OR wp_postmeta.meta_key LIKE '_order_total'
        )

Also note: Single quotes and double quotes mean different things in different databases.

score 0 · Answer 2 · answered Nov 12 '18 at 02:01

You can use GROUP_CONCAT to join the strings together. As has been pointed out by @kermit, you should use an INNER JOIN not a CROSS JOIN. I am presuming you want to keep the order total separate from the shipping details, so you will need to use conditional aggregation as well. This query should do what you want:

SELECT m.post_ID
     , GROUP_CONCAT(CASE WHEN m.meta_key LIKE '%shipping%' THEN m.meta_value END) AS shipping_info
     , MAX(CASE WHEN m.meta_key = '_order_total' THEN m.meta_value ELSE 0 END) AS order_total
FROM wp_postmeta m
INNER JOIN wp_posts p ON m.post_ID = p.ID 
WHERE p.post_status LIKE 'wc-processing' 
  AND (m.meta_key LIKE '%shipping%' OR m.meta_key LIKE '_order_total')

score 0 · Answer 3 · answered Nov 12 '18 at 02:03

0

this should do the trick its a bit more involved than on sql server but easy enough

create table if not exists test(col1 varchar(10),col2 varchar(10), col3 varchar(10));
insert into test values("this","is","test");
select concat(col1,",",col2,",",col3) as csvCol from test

delivers

csvCol
this,is,test

answered Nov 12 '18 at 02:03

quealegriamasalegre

2,887
1
13
35

Apologies all for my lack of response. I got snowed under at work and haven't had time to test these out. I will endeavour to do so in the next couple of days and will respond then. Thanks for all your help. – user10638179 Nov 25 '18 at 01:16

mySQL query returns 3 columns, I want to get values from one column concatenated into comma separated string

3 Answers3