MySQL WHERE IF Statement: switch key value of column to another if there is no value present

I'm trying to get MySQL to switch assigned key values of a column based. There problem is that value 'capacity' isn't always assigned

Currently I have something like this
WHERE
IF (m3.meta_key = 'capacity', m3.meta_key = 'capacity', m3.meta_key = 'dimensions')

The problem is that after about 20 rows is defaults to 'dimensions' instead of checking for 'capacity' again. My guess is that its because it doesn't re-que the IF again. The only way I can think of doing this is moving something to the Select portion where it applies this check on a per row statement.
m2ewAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
Please post the full query and some explanation of what is the expected result of it.
m2ewAuthor Commented:
Here is the query. Essentially I need to grab the Wcapacity first or else get the Wdimensions. As stated Wcapacity is not always assigned. I'm doing this because I want to avoid reassigning values in the database for  Wcapacity. If there is no way to pull the additional rows that aren't assigned, then I will have to update the DB to avoid incorrect CAST values assigned to Wcapacity.

SELECT m1.meta_value as productID,
      mp.ID as ModelID,
      m3.meta_key as m3metakey,
      m3.meta_value as m3metavalue,
            
      CASE
            WHEN m3.meta_value like '%<br/>%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') and SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '<br/>', -1) like '%x%'  
            THEN
                  CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '<br/>', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '<br/>', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '<br/>', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '<br/>', -1),' Gallons',''), 'x', -1)
                 as decimal(10,2))
            WHEN m3.meta_value like '%and%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') and SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1) like '%x%'  
            THEN
                  CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', -1)
                 as decimal(10,2))
            WHEN m3.meta_value like '%&%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') and SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1) like '%x%'  
            THEN
                  CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1),' Gallons',''), 'x', -1)
                 as decimal(10,2))
      
            WHEN m3.meta_value like '%<br/>%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%')
            THEN
                  CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '<br/>', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '<br/>', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '<br/>', -1),' Gallons',''), 'x', 1)
                 as decimal(10,2))
            WHEN m3.meta_value like '%and%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%')
            THEN
                  CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', -1)
                 as decimal(10,2))
            WHEN m3.meta_value like '%&%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%')
            THEN
                  CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1),' Gallons',''), 'x', 1)
                 as decimal(10,2))
               
 WHEN m3.meta_value like '%X%'
               
     THEN CAST(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'x', -1) as decimal(10,2))
                        
 WHEN m3.meta_value like '%/%' OR m3.meta_value IS NULL
 THEN
      0
 ELSE
      CAST(m3.meta_value as decimal(10,2))
 END
    as capacity,
 a.*
FROM Aposts mp
      INNER JOIN Apostmeta m1
            ON ( mp.ID = m1.post_id )
      INNER JOIN Apostmeta m2 ON ( m2.post_id = m1.post_id )
      INNER JOIN Apostmeta m3 ON ( m3.post_id =  m1.post_id )
      
      INNER JOIN Aposts a ON m1.meta_value = a.ID
      INNER JOIN Aterm_relationships e ON a.ID = e.object_id INNER JOIN Aterm_taxonomy c ON c.term_taxonomy_id = e.term_taxonomy_id
      INNER JOIN Aterms d ON c.term_id = d.term_id

       
            WHERE
                  COALESCE(m3.meta_key = 'Wcapacity', m3.meta_key = 'Wdimensions')
                  AND mp.post_type = 'modelnumbers'
                  AND mp.post_status = 'publish'
                  AND m1.meta_key = 'Wmodelproductselect'
                  AND m2.meta_key = 'Wprodtype'  
                  AND a.post_type = 'recyclingbins'
                  AND a.post_status = 'publish'
                  AND e.object_id = a.ID
                  AND d.slug = 'commercial-trash-and-recycle-bins'
            
            GROUP by mp.ID
            ORDER by CONVERT(capacity, decimal(10,2)), a.post_title  asc
Walter RitzelSenior Software EngineerCommented:
why dont you use a IN clause? Your coalesce is working pretty much like that.
[code]
m3.meta_key in ( 'Wcapacity', 'Wdimensions')

[/code]

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

m2ewAuthor Commented:
I think that is pretty close... just have to fix the cast statements to get it to override the value for capacity
m2ewAuthor Commented:
Added this around my current case statement and it worked. Thanks for the help Walter
CASE
      WHEN m3.meta_key <> 'Wdimensions'
      THEN
             ...current case statement here...
        ELSE
        0
 END
m2ewAuthor Commented:
Gave me the statement that I needed
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.