CODEWITHSUNDEEP
mysqlsql
John Smith
John Smith

Reputation: 483

Meta key:value fields returning NULL

I have a query which revolves around meta_key and meta_value fields in a database. It's almost there but when I added INNER JOIN wp_enrolments, 'class' and 'fee-types' are now returning NULL. It's like it conflicts with INNER JOIN wp_woocommerce_order_itemmeta line.

The structure of the wp_woocommerce_order_itemmeta is

The structure of the wp_enrolments table is:

I'd appreciate if you could take a look at my syntax and spot any errors.

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

Upvotes: 0

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Perhaps you need a LEFT JOIN. Also, fix the GROUP BY to match the SELECT, and don't use single quotes for column aliases:

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;

Upvotes: 0

Related Questions