Link to home
Start Free TrialLog in
Avatar of Mike Tew
Mike TewFlag for Canada

asked on

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

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

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Can you post a little sample data and expected results?

I'm guessing you have either 1 pricing row or 2, 1 with pricing and 1 with promo and want the promo price it if exists.

Also your MySQL version.

Until we get the sample data and if my assumption is correct, I would look at using a CTE to grab the value and then a query to format it.  That will make the query cleaner.

Here is what I'm thinking and it requires MySQL 8 or above:
with cte as (
	--Your full query here
	select pm.meta_key, pm.meta_value,
		row_number() over(order by case when pm.meta_key = 'pricing_promo' then 1 else 2 end) rn
	from tab1 pm
)
SELECT 
	coalesce(LENGTH(FORMAT( REPLACE ( meta_value, ',', '' ), 2 )),'Z') pricelength,
	FORMAT(coalesce(REPLACE ( meta_value, ',', '' ),0), 2 ) price
FROM cte
where rn=1

Open in new window

Avatar of Mike Tew

ASKER

Thanks slightwv. We're on 10.3+ MariaDB, which looks like it's compatible. I will try it out.
results should be a row like this. Price should load meta_value based on meta_key=promo_pricing first, otherwise then load meta_value based on meta_key=pricing
 
pricelengthpriceproductid
66454054

Yes it is.

I meant to post my Fiddle test case.  Since you gave me MariaDB, here's that fiddle:
https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=a5e57583020dd6a5dec14a32f72e6d0b

If something doesn't work, please add to the fiddle and tell me what I need to fix.
Hi slightwv. With statement not accepted in the syntax. Is there another way to do this using EXISTS or NOT EXISTS? Also, I need to run joins to ensure proper price keys and values are grabbed for the products. Thanks
Not sure why WITH isn't accepted.  The fiddle I posted is MariaDB 10.3.

I had a small syntax error in my comment.  I forgot MySQL has to have a space after the '--'.

Anyway, since it is only one CTE, you can make it an inline view:
SELECT 
	coalesce(LENGTH(FORMAT( REPLACE ( meta_value, ',', '' ), 2 )),'Z') pricelength,
	FORMAT(coalesce(REPLACE ( meta_value, ',', '' ),0), 2 ) price
FROM (
	-- Your full query here
	select pm.meta_key, pm.meta_value,
		row_number() over(order by case when pm.meta_key = 'pricing_promo' then 1 else 2 end) rn
	from tab1 pm
) cte
where rn=1

Open in new window


Fiddle with both:
https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=352d860d24003b0e02a2f4f3b68c1a76

>>Also, I need to run joins to ensure proper price keys and values are grabbed for the products

notice the:  -- Your full query here

Take the core query you had with all the joins and put it in place of my simple tab1 table.

All you'll need to to is add the ROW_NUMBER column to what you had in the columns in the select.

Well, and clean up all the CASE stuff you had.
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial