How to unify fields?

Question

I have this query

SELECT DISTINCT wp_posts.ID, wp_posts.post_excerpt, wp_posts.post_name, wp_posts.guid,
(CASE WHEN wp_postmeta.meta_key = 'product_ids'  AND  wp_posts.`ID` = wp_postmeta.`post_id` THEN wp_postmeta.`meta_value` END) AS 'product_ids',
(CASE WHEN wp_postmeta.meta_key = 'coupon_amount'      AND  wp_posts.`ID` = wp_postmeta.`post_id` THEN wp_postmeta.`meta_value` END) AS 'coupon_amount',
(CASE WHEN wp_postmeta.meta_key = 'discount_type' AND  wp_posts.`ID` = wp_postmeta.`post_id` THEN wp_postmeta.`meta_value` END) AS 'discount_type',
(CASE WHEN wp_postmeta.meta_key = 'free_shipping'      AND  wp_posts.`ID` = wp_postmeta.`post_id` THEN wp_postmeta.`meta_value` END) AS 'free_shipping',
(CASE WHEN wp_postmeta.meta_key = 'expiry_date'        AND  wp_posts.`ID` = wp_postmeta.`post_id` THEN wp_postmeta.`meta_value` END) AS 'expiry_date'   
FROM wp_posts 
RIGHT JOIN wp_postmeta
ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.post_type = 'shop_coupon' AND wp_postmeta.meta_key IN ('discount_type','coupon_amount', 'product_ids')

This way I can get the name of a coupon, its amount, and its discount type, but the query shows me as follows:

[ enter image description here ]

How can I make everything appear in 1 single row? Like this:

[ enter image description here ]

And the important thing, I also need to filter it by product ID, in the photo you can see that the ID is 725

forpas · Answer 1 · 2020-11-27T19:07:48.960

0

For this sample data you can do it with aggregation:

SELECT wp_posts.ID, wp_posts.post_excerpt, wp_posts.post_name, wp_posts.guid,
  MAX(CASE WHEN wp_postmeta.meta_key = 'product_ids'  AND wp_posts.`ID` = wp_postmeta.`post_id` THEN wp_postmeta.`meta_value` END) AS product_ids,
  MAX(CASE WHEN wp_postmeta.meta_key = 'coupon_amount' AND wp_posts.`ID` = wp_postmeta.`post_id` THEN wp_postmeta.`meta_value` END) AS coupon_amount,
  MAX(CASE WHEN wp_postmeta.meta_key = 'discount_type' AND wp_posts.`ID` = wp_postmeta.`post_id` THEN wp_postmeta.`meta_value` END) AS discount_type,
  MAX(CASE WHEN wp_postmeta.meta_key = 'free_shipping' AND wp_posts.`ID` = wp_postmeta.`post_id` THEN wp_postmeta.`meta_value` END) AS free_shipping,
  MAX(CASE WHEN wp_postmeta.meta_key = 'expiry_date' AND wp_posts.`ID` = wp_postmeta.`post_id` THEN wp_postmeta.`meta_value` END) AS expiry_date   
FROM wp_posts RIGHT JOIN wp_postmeta
ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.post_type = 'shop_coupon' 
  AND wp_postmeta.meta_key IN ('discount_type','coupon_amount', 'product_ids')
GROUP BY wp_posts.ID, wp_posts.post_excerpt, wp_posts.post_name, wp_posts.guid

Also with this condition:

wp_posts.post_type = 'shop_coupon'

since it is applied in the WHERE clause, you are actually doing an INNER join instead of a RIGHT join.
If this isn't what you want move it to the ON clause:

FROM wp_posts RIGHT JOIN wp_postmeta
ON wp_posts.ID = wp_postmeta.post_id AND wp_posts.post_type = 'shop_coupon' 
WHERE wp_postmeta.meta_key IN ('discount_type','coupon_amount', 'product_ids')

Also, if you actually want an INNER join, then this condition:

AND wp_posts.`ID` = wp_postmeta.`post_id`

inside the CASE expressions, is not needed, because it is true, since it is applied in the ON clause.

edited Nov 27 '20 at 19:07

answered Nov 27 '20 at 18:49

forpas

160,666
10
38
76

Thank you very much friend !! And how do I tell the meta_key to identify if the value is 'product_ids' and the meta_value to identify if the value is 725 (this would be the product_id). in this way to be able to identify the coupons for each product – user9520834 Nov 27 '20 at 19:25
The condition for `'product_ids'` is included in the list of the operator IN, if this is what you are asking. Also, the MAX() function, when applied to values like null, null, 725 will return 725. – forpas Nov 27 '20 at 19:32
I would like to show only the coupons created for each product, example: for product 725 = 2 coupons for product 687 = 1 coupon – user9520834 Nov 27 '20 at 20:06
This is what you get with this query: for product 725 = 2 coupons, for product 687 = 1 coupon, as I see in the results of your query, if all 4 columns: wp_posts.ID, wp_posts.post_excerpt, wp_posts.post_name, wp_posts.guid have the same values. – forpas Nov 27 '20 at 20:09
Correct, but there I show all the coupons, but how do I only show the coupons for each product_id. For example, if someone walks in to see product 725, I will show them only 2 coupons. If someone goes to see product 687, they will only see 1 coupon. This I show with php – user9520834 Nov 27 '20 at 20:13
My answer is about the question as you posted it, meaning about removing redundant rows and show only 1 row for each combination of wp_posts.ID, wp_posts.post_excerpt, wp_posts.post_name, wp_posts.guid. I don't know what a coupon is, and what a product_id is. If you have a new requirement you can ask a new question. – forpas Nov 27 '20 at 20:16
please check https://stackoverflow.com/questions/42769306/how-to-retrieve-a-list-of-woocommerce-orders-which-use-a-particular-coupon/42771538#42771538 – user9520834 Nov 28 '20 at 04:47

How to unify fields?

1 Answers1