I am trying to join two tables. The issue is one of the tables actually has a comma-separated list inside the cell. I need to join on the 'csv cell.' Using my salad based example I want bacon to join with bacon,turkey and give me a cobb_salad

   SELECT tbl_a.item, tbl_b.item, tbl_b.salad 
     FROM tbl_a
LEFT JOIN tbl_b
       ON  tbl_a.item LIKE CONCAT('%', tbl_b.item, '%')

I tried the example above and a few other varations. I realize this is not going to be an efficent query. The issue is that the underlying dataset predates me. Luckily I only need to run the query once.

Expected Result
+--------+-开发者_如何学Python-----------+
| item   |   salad    |
+--------+------------|
|  ham   | chef_salad |
| bacon  | cobb_salad |
| turkey | cobb_salad |
+---------------------+


tbl_a 
+------+
| item |
+------+
|bacon | 
| ham  | 
|turkey|
+------+  

tbl_b
+--------------+------------+
|     item     |  salad     |
+--------------+------------+
| ham          | chef_salad |
| bacon,turkey | cobb_salad |
+--------------|------------+

---

NOTICE:

I show in your db, there are many space characters in your tables. Please try to trim it before inserting into your db. Ex:
From:
ham => [space]ham[space]
To:
ham => [no-space]ham[no-space]
Hope you understand what I said.

---

You can try:
It works for me:

mysql> use test;
Database changed
mysql> SELECT tbl_a.item as item_a, tbl_b.item as item_b, tbl_b.salad
    -> FROM tbl_a LEFT JOIN tbl_b 
    -> ON FIND_IN_SET(tbl_a.item, tbl_b.item);
+--------+--------------+------------+
| item_a | item_b       | salad      |
+--------+--------------+------------+
| bacon  | bacon,turkey | cobb_salad |
| ham    | ham          | chef_salad |
+--------+--------------+------------+
2 rows in set (0.00 sec)

UPDATE:

Here is my tables:

mysql> select * from tbl_a;
+-------+
| item  |
+-------+
| bacon |
| ham   |
+-------+

AND

mysql> select * from tbl_b;
+--------------+------------+
| item         | salad      |
+--------------+------------+
| bacon,turkey | cobb_salad |
| ham          | chef_salad |
+--------------+------------+