troubleshooting Question

How do I toggle inner join results using CASE and IF statements in mysql?

Avatar of Mike Tew
Mike TewFlag for Canada asked on
Databases* joins* php mysql
6 Comments1 Solution8 ViewsLast Modified:
The below query defaults to pm.meta_key = 'pricing' and thusly sets the price value. I have 2 CASE statements to set pricelength and price

In the WHERE clause, I added an IF statement to pm.meta_key = to attempt to set pricing_promo first. However, it's still setting price from pm.meta_value when pm.meta_key = 'pricing'.

How can I get it to grab pm.meta_value from the table row by looking up pm.meta_key = 'pricing_promo' first? And, if it's empty, look up pm.meta_key = 'pricing' to then grab pm.meta_value in the associated table row?

SELECT 
CASE 
WHEN (pm.meta_key = 'pricing_promo' && LENGTH( pm.meta_value ) > 0)
THEN LENGTH(FORMAT( REPLACE ( pm.meta_value, ',', '' ), 2 )) 
WHEN (pm.meta_key = 'pricing' && LENGTH( pm.meta_value ) > 0)
THEN LENGTH(FORMAT( REPLACE ( pm.meta_value, ',', '' ), 2 )) 
ELSE 'Z' 
END as pricelength, 
CASE 
WHEN pm.meta_key = 'pricing_promo' AND pm.meta_value <> ""
THEN FORMAT( REPLACE ( pm.meta_value, ',', '' ), 2 ) 
WHEN pm.meta_key = 'pricing' 
THEN FORMAT( REPLACE ( pm.meta_value, ',', '' ), 2 ) 
ELSE FORMAT('0',2) END AS price
, a.* 
FROM wp__posts a INNER JOIN wp__term_relationships e ON a.ID = e.object_id 
INNER JOIN wp__term_taxonomy c ON c.term_taxonomy_id = e.term_taxonomy_id 
INNER JOIN wp__terms d ON c.term_id = d.term_id 
INNER JOIN wp__postmeta pm ON a.ID = pm.post_id
WHERE e.object_id = a.ID AND d.slug = 'product-category' 
AND a.post_status='publish' 
AND a.post_type = 'product'  
AND pm.meta_key = (IF (pm.meta_key = 'pricing_promo' ,'pricing_promo','pricing'))
GROUP BY a.id 
Order By pricelength asc, price asc, a.post_title asc LIMIT 28 offset 0
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros