MYSQL IF Field contains THIS make other Field THAT

case, concatenation, if-statement, mysql

Solution

This should be what you're looking for:

   SELECT
       IF (MT.ITEM LIKE '%USED-%', 'Used Restaurant Equipment', CT.TIER1) AS TIER1,
       CT.TIER2
   FROM
       trx.table1 MT
           LEFT JOIN
       trx.table2 CT1 ON MT.ITEM = CT1.ITEM
           LEFT JOIN
       trx.table3 CT ON CT1.WEB_NUM = CT.WEBNUMBER
   GROUP BY
       IF (MT.ITEM LIKE '%USED-%', 'Used Restaurant Equipment', CT.TIER1)
   LIMIT 0 , 15

Problem

I have a field called `ITEM` in my table1. If `ITEM` contains `%USED-%` (USED-) then I want to make its `TIER1` `Used Restaurant Equipment` so it groups accordingly. This is my current statement, how can I achieve the above with an `IF` or `CASE`? ``` select CT.TIER1, CT.TIER2 from trx.table1 MT LEFT JOIN trx.table2 CT1 ON MT.ITEM = CT1.ITEM LEFT JOIN trx.table3 CT ON CT1.WEB_NUM = CT.WEBNUMBER GROUP BY CT.TIER1 LIMIT 0 , 15 ``` Current Output: ``` TIER1 TIER2 NULL NULL Business Type Ice Cream Equipment Chinese Food Equipment Ricers Concession Equipment Concession Accessories Cooking Equipment Fryers Food Preparation Food Preparation Supplies Food Storage Storage Racks Food Warming Equipment Hot Food Wells ``` I expect that there still be some `NULL` Tier1 and Tier2's but I want it to also display a TIER1 as 'Used Restaurant Equipment', similar to this: ``` TIER1 TIER2 NULL NULL Used Restaurant Equipment NULL Business Type Ice Cream Equipment Chinese Food Equipment Ricers Concession Equipment Concession ```

Original source