CASE statement and how to use values from it

Question

I have a query

select 
    p.ID as order_id,
    p.post_date,
    i.order_item_id,
    max( CASE WHEN im.meta_key = '_product_id' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as product_id,
    max( CASE WHEN im.meta_key = '_qty' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as qty,   
    max( CASE WHEN prm.meta_key = '_sku' and im.meta_value = prm.post_id THEN prm.meta_value END ) as sku,
    max( CASE WHEN prm.meta_key = '_regular_price' and im.meta_value = prm.post_id THEN prm.meta_value END ) as product_price
from 
    wp_posts as p,
    wp_postmeta as pm,
    wp_woocommerce_order_items as i,
    wp_woocommerce_order_itemmeta as im,
    wp_postmeta as prm    
where 
    p.post_type = 'shop_order'
    and p.ID = pm.post_id
    and p.ID = i.order_id
    and p.post_date BETWEEN '2016-01-14 00:00:00' AND '2016-01-14 23:59:59'
    and p.post_status = 'wc-processing'
    and p.ID = i.order_id
    and i.order_item_id = im.order_item_id
group by
    p.ID  

but I'm stuck on obtaining sku and product_price using product_id (both values came as NULL), when on phpMyAdmin I will do search by its value I can get SKU and product price meta_value as well other values for meta_key located in the wp_postmeta table.

Based on comments below and helpful directions from an answer I have made JOIN version query which works but is slow:

select 
    p.ID as order_id,
    p.post_date,
    i.order_item_id,
    max( CASE WHEN im.meta_key = '_product_id' THEN im.meta_value END ) as product_id,
    max( CASE WHEN imq.meta_key = '_qty' THEN imq.meta_value END ) as qty,
    max( CASE WHEN prm.meta_key = '_sku' THEN prm.meta_value END ) as sku,
    max( CASE WHEN prm.meta_key = '_regular_price' THEN prm.meta_value END ) as product_price
from 
    wp_posts as p
join  
    wp_postmeta as pm
on
    p.ID = pm.post_id
join
    wp_woocommerce_order_items as i
on 
    p.ID = i.order_id
join
    wp_woocommerce_order_itemmeta as im
on
    i.order_item_id = im.order_item_id
join
    wp_woocommerce_order_itemmeta as imq
on
    i.order_item_id = imq.order_item_id
join  
    wp_postmeta as prm
on
    im.meta_value = prm.post_id
where 
    p.post_type = 'shop_order'
    and p.post_date BETWEEN '2016-01-14 00:00:00' AND '2016-01-14 23:59:59'
    and p.post_status = 'wc-processing'
group by
    p.ID  

My question is why do I need to create JOIN for each CASE statement - other way for an example qty returns value as NULL.

Answer 1

Well - I have ended up removing CASEstatement and moving towards explicit JOIN syntax.

select 
    p.ID as order_id,
    p.post_date,
    i.order_item_id,
    im.meta_value as product_id,
    imq.meta_value as qty,
    prm.meta_value as sku,
    prp.meta_value as product_price
from 
    wp_posts as p
join  
    wp_postmeta as pm
on
    p.ID = pm.post_id
join
    wp_woocommerce_order_items as i
on 
    p.ID = i.order_id
join
    wp_woocommerce_order_itemmeta as im
on
    i.order_item_id = im.order_item_id
join
    wp_woocommerce_order_itemmeta as imq
on
    i.order_item_id = imq.order_item_id
join  
    wp_postmeta as prm
on
    im.meta_value = prm.post_id
join  
    wp_postmeta as prp
on
    im.meta_value = prp.post_id
where 
    p.post_type = 'shop_order'
    and p.post_date BETWEEN '2016-01-14 00:00:00' AND '2016-01-14 23:59:59'
    and p.post_status = 'wc-processing'
    and im.meta_key = '_product_id'
    and imq.meta_key = '_qty'
    and prm.meta_key = '_sku'
    and prp.meta_key = '_regular_price'
group by
    p.ID  

Thanks to spencer7593 for good directions.

Answer 2

There doesn't appear to be any predicate in the join to wp_postmeta (alias prm in the query.) So that's a cross join operation. Performing a cross join is valid; but it's unlikely that's the result you want.

Ditch the old-school comma syntax for the join operation. Use the ANSI JOIN keyword. And relocate the join predicates from the WHERE clause to an appropriate ON clause.

Some of the conditional tests in the CASE expression are unnecessary, because they are redundant. They will always evaluate to TRUE.

The query includes this join predicate (in the WHERE clause)

i.order_item_id = im.order_item_id

That same condition appears again in the CASE expression:

CASE WHEN im.meta_key = '_product_id' and i.order_item_id = im.order_item_id THEN

There's no need to repeat that condition in the CASE expression, because it's going to evaluate to TRUE for every row that is returned.

CASE WHEN im.meta_key = '_product_id' THEN

---

Given the "missing" join predicate for the join to wp_postmeta (aliased as prm)... and given the condition we see in the other CASE expressions

im.meta_value = prm.post_id

We suspect that you want to include that condition as a join predicate.

Absent sample rows, we're just guessing. My guess at what you are trying to achieve, is a result something like this:

SELECT p.id as order_id
     , p.post_date
     , i.order_item_id
     , MAX( CASE WHEN im.meta_key = '_product_id'     THEN im.meta_value  END ) as product_id
     , MAX( CASE WHEN im.meta_key = '_qty'            THEN im.meta_value  END ) as qty
     , MAX( CASE WHEN prm.meta_key = '_sku'           THEN prm.meta_value END ) as sku
     , MAX( CASE WHEN prm.meta_key = '_regular_price' THEN prm.meta_value END ) as product_price
  FROM wp_posts p
  JOIN wp_postmeta pm
    ON pm.post_id = p.ID
  JOIN wp_woocommerce_order_items i
    ON i.order_id = p.ID
  JOIN wp_woocommerce_order_itemmeta im
    ON im.order_item_id = i.order_item_id
  JOIN wp_postmeta as prm
    ON prm.post_id = im.meta_value
 WHERE p.post_type = 'shop_order'
   AND p.post_status = 'wc-processing'
   AND p.post_date >= '2016-01-14'
   AND p.post_date <  '2016-01-15'
 GROUP BY p.ID  

This doesn't take into account the possibility of "missing" rows from prm, perhaps you want to use an outer join operation.

And with i.order_item_id in the SELECT list, and the CASE expressions returning values from im, it looks like you might want to include i.order_item_id in the GROUP BY clause.

But again, those are just guesses.