Question 3478 in Woocommerce Question posted in
The official Woocommerce documentation can be found here.

If value does not exist return null in sql – Woocommerce

I have a table (woocommerce) which has the following structure

enter image description here

I want to get the meta_value corresponding to certain meta_key and if they don’t exist then return NULL. This is what i have right now

SELECT `meta_value`
FROM `wp_woocommerce_order_itemmeta`
WHERE `order_item_id` = 66
AND `meta_key` IN ("pa_brewing-method", "pa_size", "Sold By")

Result :

meta_value
BeanDeck

How can i show this as?

meta_value 
NULL
NULL
BeanDeck
Tags:

2

Answers


  1. 0

    You aren’t correlating the inner and outer queries. If there’s at least one product with category_id = 90 the inner query will return some rows, and thus the NOT EXISTS condition will always be false, and the outer query will return no rows. You need to add a condition to specify the inner query and the outer query refer to the same product:

    SELECT * 
FROM  `oc_product_to_category` a
WHERE NOT EXISTS (
  SELECT * 
  FROM   `oc_product_to_category` b
  WHERE  category_id = 90 AND
         a.product_id = b.product_id -- Here!
)
    Login or Signup to reply.
  2. 0

    Instead of filtering using IN(), you could LEFT JOIN on to the list of keys you’re interested in.

    SELECT
  filter.order_item_id,
  filter.meta_key,
  meta.meta_value
FROM
(
            SELECT 66 AS order_item_id, 'pa_brewing-method' AS meta_key
  UNION ALL SELECT 66 AS order_item_id, 'pa_size' AS meta_key
  UNION ALL SELECT 66 AS order_item_id, 'Sold By' AS meta_key
)
  AS filter
LEFT JOIN
  wp_woocommerce_order_itemmeta   AS meta
    ON  meta.order_item_id = filter.order_item_id
    AND meta.meta_key      = filter.meta_key
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search