Custom MySQL Query with logic

Question

I have wp e-commerce plugin installed and custom building custom global search

My query looks like this

SELECT * FROM aka_v_posts 
LEFT JOIN aka_v_postmeta 
ON aka_v_posts.ID=aka_v_postmeta.post_id 
AND ((aka_v_postmeta.meta_key='_wpsc_special_price' 
      AND aka_v_postmeta.meta_value > '0') 
    OR aka_v_postmeta.meta_key='_wpsc_price')
WHERE ( post_title LIKE '%32%' OR post_content LIKE '%32%' )
AND ( post_status = 'publish' OR post_status = 'private' ) 
AND post_type = 'wpsc-product' 
AND aka_v_postmeta.meta_value BETWEEN 0 AND 999999 
ORDER BY aka_v_posts.post_date DESC, aka_v_postmeta.meta_value + 0 ASC

The thing I'm doing here, joining prices where if product is on sale, get post with sale meta_value, else get with meta_key "_wpsc_price"

Of course this part is not working

AND ((aka_v_postmeta.meta_key='_wpsc_special_price' 
      AND aka_v_postmeta.meta_value > '0') 
    OR aka_v_postmeta.meta_key='_wpsc_price')

It queries products right when product is not on sale, but when it is, it displays it twice, one with "_wpsc_price" meta_key and other with "_wpsc_special_price" Because 1 OR 1 is 1. So this logic is totally messy.

I did a little research with my little of knolidge of mysql and found CASE WHEN condition but never found a way to apply it for current issue.

Any suggestions? Of course doing it without CASE WHEN would be better

Answer 1

Thank's everyone for your attention.

If anybody will need to query database with two postmeta, here is the code

SELECT p.* 
, CASE WHEN m1.meta_value=0 OR m1.meta_value IS NULL 
THEN m2.meta_value 
ELSE m1.meta_value 
END meta_value 
FROM aka_v_posts p 
LEFT JOIN aka_v_postmeta m1 ON p.id=m1.post_id AND m1.meta_key='_wpsc_special_price' 
LEFT JOIN aka_v_postmeta m2 ON p.id=m2.post_id AND m2.meta_key='_wpsc_price' 
WHERE post_title LIKE '%32%'
AND post_type = 'wpsc-product' 
ORDER BY aka_v_posts.post_date DESC

The thing to keep in mind (at least I was doing it wrong) is that you need to check for meta_values before the FROM keyword, to select those meta_values.