Merge queries into one

Question

I have the query below which returns OrderID, order_item_id, _product_id, Quantity.

SELECT `wp_woocommerce_order_items`.`order_id` AS 'OrderID',
    `wp_woocommerce_order_items`.`order_item_id`,
    MAX(CASE WHEN `wp_woocommerce_order_itemmeta`.`meta_key` = '_product_id' THEN `wp_woocommerce_order_itemmeta`.`meta_value` ELSE NULL END) AS '_product_id',
    MAX(CASE WHEN `wp_woocommerce_order_itemmeta`.`meta_key` = '_qty' THEN `wp_woocommerce_order_itemmeta`.`meta_value` ELSE NULL END) AS 'Quantity'
FROM `wp_woocommerce_order_items`, `wp_woocommerce_order_itemmeta`
WHERE `wp_woocommerce_order_items`.`order_id` = 50
AND `wp_woocommerce_order_items`.`order_item_id` = `wp_woocommerce_order_itemmeta`.`order_item_id`
AND `wp_woocommerce_order_items`.`order_item_type` = 'line_item'
AND (`wp_woocommerce_order_itemmeta`.`meta_key` = '_qty'
    OR `wp_woocommerce_order_itemmeta`.`meta_key` = '_product_id')
GROUP BY `wp_woocommerce_order_items`.`order_item_id`

Returns:

+---------+---------------+-------------+----------+
| OrderID | order_item_id | _product_id | Quantity |
+---------+---------------+-------------+----------+
|      50 |            11 | 27          | 3        |
|      50 |            18 | 42          | 1        |
+---------+---------------+-------------+----------+

I then loop through each row in the above result in php to use the _product_id in the query below to match wp_postmeta.post_id.

SELECT `post_id`,  
    MAX(CASE WHEN `wp_postmeta`.`meta_key` = '_sku' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS 'PartNumber',
    MAX(CASE WHEN `wp_postmeta`.`meta_key` = '_price' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS 'UnitPrice'
FROM `wp_postmeta`
WHERE `wp_postmeta`.`post_id` = $PRODUCT_ID
AND (`wp_postmeta`.`meta_key` = '_sku'
    OR `wp_postmeta`.`meta_key` = '_price')

Returns:

+---------+------------+-----------+
| post_id | PartNumber | UnitPrice |
+---------+------------+-----------+
|      27 | MOO123     | 50        |
+---------+------------+-----------+

+---------+------------+-----------+
| post_id | PartNumber | UnitPrice |
+---------+------------+-----------+
|      42 | MOO345     | 145       |
+---------+------------+-----------+

Is there a way to combine the two, so it adds PartNumber and UnitPrice on to the first query result and returns data like below?

+---------+---------------+-------------+----------+------------+-----------+
| OrderID | order_item_id | _product_id | Quantity | PartNumber | UnitPrice |
+---------+---------------+-------------+----------+------------+-----------+
|      50 |            11 | 27          | 3        | MOO123     | 50        |
|      50 |            18 | 42          | 1        | MOO345     | 145       |
+---------+---------------+-------------+----------+------------+-----------+

Table data found here: http://pastebin.com/3EN5P4fD

Thanks

score 1 · Accepted Answer · edited May 23 '17 at 12:12

~~How about using JOIN statement?~~ (Please see the footnote below about the JOIN problem.

SELECT `i`.`order_id` AS 'OrderID',
       `i`.`order_item_id`,
       @productID := (SELECT MAX(
                CASE 
                    WHEN `meta_key` = '_product_id' 
                    THEN `meta_value` 
                    ELSE NULL 
                END)
           FROM `wp_woocommerce_order_itemmeta`
          WHERE `order_item_id` = `i`.`order_item_id`
            AND (`meta_key` = '_qty'
                 OR `meta_key` = '_product_id')) AS '_product_id',

       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = '_qty' 
                       THEN `meta_value` 
                       ELSE NULL 
                   END) 
          FROM `wp_woocommerce_order_itemmeta`
         WHERE `order_item_id` = `i`.`order_item_id`
           AND (`meta_key` = '_qty'
                OR `meta_key` = '_product_id')) AS 'Quantity',

       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = '_sku' 
                       THEN `meta_value` 
                       ELSE NULL 
               END)
          FROM `wp_postmeta`
         WHERE `post_id` = @productID
           AND (`meta_key` = '_sku'
                OR `meta_key` = '_price')) AS 'PartNumber',

       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = '_price' 
                       THEN `meta_value` 
                   ELSE NULL 
               END)
          FROM `wp_postmeta`
         WHERE `post_id` = @productID
           AND (`meta_key` = '_sku'
                OR `meta_key` = '_price')) AS 'UnitPrice'

 FROM `wp_woocommerce_order_items` AS `i` 
WHERE `i`.`order_id` = 50
  AND `i`.`order_item_type` = 'line_item'
GROUP BY `i`.`order_item_id`

I haven't tested the above statement, but it should get you started.

There is also another post in the Stack Overflow website: MySQL: Use CASE/ELSE value as join parameter that talks about using CASE as a JOIN parameter. Check it out!

Footnote:

My initial tries with JOIN statements failed due to the error: "Error Code: 1111. Invalid use of group function". After reading the post MySQL LEFT JOIN, GROUP BY and ORDER BY not working as required I changed the JOIN statements into sub-queries.

I get the following error, any ideas? #1054 - Unknown column 'wp_postmeta.meta_key' in 'where clause'. Thanks — arbme, Apr 28 '13 at 01:06
Please see my edit to the query. I changed `wp_postmeta` to `p`. — GregD, Apr 28 '13 at 01:08
New error: #1111 - Invalid use of group function. Thanks again. — arbme, Apr 28 '13 at 01:10
Without the knowledge of your table structures and/or sample data it is very difficult to correct my own query or get more precise answer from other users. Please post this information for us. — GregD, Apr 28 '13 at 01:11
Thank you, I will check it out. meanwhile, I did another edit to my query, see if it works — GregD, Apr 28 '13 at 01:24
New error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 46 :). Thanks for the dedication. — arbme, Apr 28 '13 at 01:27
MySQL barbarian! Thanks your a hero, also good links to read. — arbme, Apr 28 '13 at 01:48

Merge queries into one

1 Answers1