mysql - 元 key :value fields returning NULL

Question

我有一个围绕数据库中的meta_key 和meta_value 字段的查询。几乎就在那里，但是当我添加 INNER JOIN wp_enrolments 时，“class”和“fee-types”现在返回 NULL。这就像它与 INNER JOIN wp_woocommerce_order_itemmeta 行冲突。

wp_woocommerce_order_itemmeta 的结构是

• ID
• 元 key
• 元值

wp_enrolments 表的结构为:

• ID
• 名字
• 姓氏
• 等等

如果您能看一下我的语法并发现任何错误，我将不胜感激。

SELECT 
  O.order_item_id, O.order_item_name,
  E.firstname,
  MAX(CASE WHEN OIM.meta_key = 'class' THEN OIM.meta_value END) AS 'class',
  MAX(CASE WHEN OIM.meta_key = 'fee-type' THEN OIM.meta_value END) AS 'fees'
FROM wp_woocommerce_order_items O
INNER JOIN wp_woocommerce_order_itemmeta OIM ON O.order_item_id = OIM.order_item_id
INNER JOIN wp_enrolments E ON E.id = OIM.meta_value AND OIM.meta_key = 'enrolment_id'
GROUP BY O.order_item_id

Answer 1

也许您需要LEFT JOIN。另外，修复 GROUP BY 以匹配 SELECT，并且不要对列别名使用单引号:

SELECT O.order_item_id, O.order_item_name, E.firstname,
       MAX(CASE WHEN OIM.meta_key = 'class' THEN OIM.meta_value END) AS class,
       MAX(CASE WHEN OIM.meta_key = 'fee-type' THEN OIM.meta_value END) AS fees
FROM wp_woocommerce_order_items O JOIN
     wp_woocommerce_order_itemmeta OIM
     ON O.order_item_id = OIM.order_item_id LEFT JOIN
     wp_enrolments E
     ON E.id = OIM.meta_value AND
        OIM.meta_key = 'enrolment_id'
GROUP BY O.order_item_id, O.order_item_name, E.firstname;