If value does not exist return null in sql-CodePudding

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

CodePudding user response：

I tried using a dummy table and I guess this output is similar to what you expected


-- create
CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NULL
);

-- insert
INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', null);

-- fetch 
SELECT (CASE 
        WHEN dept ISNULL THEN 'NULL' 
        ELSE dept 
        END)
        
        from EMPLOYEE;
        
SELECT ' WITHOUT CASE ';
        
SELECT dept FROM EMPLOYEE

output

Output:

Sales
Accounting
NULL
 WITHOUT CASE 
Sales
Accounting

CodePudding user response：

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!
)

CodePudding user response：

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