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
* joins* php mysqlDatabases
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

Open in new window

ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
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