I'm getting the following error message when I run the query below.
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'
First, I'm wondering if there's a better way to write my query. Look at the Where clause. Notice I'm trying to combine wildcards with a field name as the search criteria. I was able to get this to work in Access, but I'm unsure of the syntax in MySQL.
If there's no better syntax, then how do I fix the error message? In one post on EE someone suggested using _latin1, but I don't know how to apply that to this query. Alternatively, I could potentially change my data to latin1... but I don't even know where I'm using UTF8 b/c my tables are latin1 (as far as I can tell).
SELECT shipping_details.orders_id, shipping_criteria.Hold_Till_Next_Week, shipping_criteria.Box_Type
WHEN Orders.shipping_method like '%ground%' THEN 'GND'
WHEN Orders.shipping_method like '%2nd day%' THEN '2DA'
WHEN Orders.shipping_method like '%next day%' THEN '1DA'
WHEN Orders.shipping_method like '%canada%' THEN 'STD'
WHEN Orders.shipping_method like '%expedited%' THEN 'XPD'
WHEN Orders.shipping_method like '%Free Shipping%' THEN 'GND'
END as shipping_type,
DATE_FORMAT(now(),'%a') as Process_Day,
WHEN Orders.shipping_allowed Like '%GND%' Or Orders.shipping_allowed Like '%XPD%' Or Orders.shipping_allowed Like '%STD%' THEN 'Cold'
END as Destination
) AS shipping_details
WHERE shipping_criteria.Active=1 AND shipping_criteria.Destination Like CONCAT('%', shipping_details.destination, '%') AND shipping_criteria.Method Like CONCAT('%', shipping_details.shipping_type, '%') AND shipping_criteria.Day_Sent_To_CCG Like CONCAT('%', shipping_details.Process_Day, '%');