sqlleft-join

Sql join with like condition not working for non exact matches

I am trying to join my expenses table with like query to categorize them into defined categories. I am trying if my expenses table even have a mention of a full keyword from lookup table, it should categorize them. Example, it does not show 'fine Esso brampton' categorized as 'Gas'. But the like join only works for exact match. Please suggest what's the issue here:

SELECT expenses.Description, expenses.Amount, COALESCE(lookup.Category, 'Others') as Category
FROM expenses
LEFT join lookup ON lookup.Description LIKE CONCAT('%', expenses.Description,'%')

enter image description here

enter image description here

Solution

  • it seems to me or need to swap fields like

    SELECT expenses.Description, expenses.Amount, COALESCE(lookup.Category, 'Others') as Category
FROM expenses
LEFT join lookup ON expenses.Description LIKE CONCAT('%', lookup.Description ,'%')