Mike Tew
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?
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
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
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
pricelength | price | productid |
6 | 645 | 4054 |
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.
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.
ASKER
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:
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
Open in new window