sql - Need advice about JOIN by LIKE operator -

i have 2 data tables, 1 contain customer data such customer id , used bonus codes. second table internal notes, every note write customer there, example: gave customer 12 bonus code gft100. know need join 2 table based on bonus code, want every bonus code player used find relevant note in notes table.

table 1: used bonus codes

customerid | coupon_code | dateofusege       --------------------------------------            12     |   aaa25     | 2016-09-10          -------------------------------------     12     |   bbb13     | 2016-09-10 --------------------------------------     17     |   ccc14     | 2016-09-10 

table2:customer notes

customerid|   date   |         text ---------------------------------------------    12     |2016-09-07| gave bonus aaa25      ----------------------------------------------    12     |2016-09-07| customer ----------------------------------------------    17     |2016-09-06| gave bonus code ccc14  

desired output: each used code in table 1 add relevant note table 2

customerid | coupon_code | dateofusege  |         text          |     ----------------------------------------------------------------            12     |   aaa25     | 2016-09-10   | gave bonus aaa25      |  ----------------------------------------------------------------     17     |   ccc14     | 2016-09-10   | gave bonus code ccc14 | ----------------------------------------------------------------- 

how can that?

i'd advise adding nullable coupon_code column notes table (assuming note may pertain 0 or 1 coupon code) , recording optional code each note when applicable. searching code in free text values return false positives

... but, join should started

select a.customerid, a.coupon_code, a.dateofusege, b.text `used_bonus_codes` inner join `customer_notes` b   on a.customerid = b.customerid   , b.text concat('%', a.coupon_code, '%')