0

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
+--------+------------+
| 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 |
+--------------|------------+
Cœur
  • 37,241
  • 25
  • 195
  • 267
personalt
  • 810
  • 3
  • 13
  • 26
  • Can you format your question? I don't understand what's being asked here? – vietean Oct 04 '11 at 03:45
  • Visit [Editing-help](http://stackoverflow.com/editing-help) to know how to format your question. – vietean Oct 04 '11 at 03:47
  • 1
    Possible [duplicate](http://stackoverflow.com/questions/5457471/mysql-in-list-only-validates-first-id-in-list-maybe-a-blob-issue/5457653). – jswolf19 Oct 04 '11 at 03:52
  • Sorry about that, hit submit before I worked out the formatting.. corrected it above to make clear.. – personalt Oct 04 '11 at 04:02
  • Please don't vote down the question. He is just a newbie on SO. – vietean Oct 04 '11 at 04:06
  • 2
    Just as a side-note, this is a very strange way of storing data in a database. For this application, you would generally have 3 tables: A table of ingredients, a table of dishes, and a table while links one to many ingredients to a dish. – Ozzah Oct 04 '11 at 04:30
  • I 100% agree.... The original table is table-b, it is part of some legacy system that has been around 15 years and can only store flat files The flat files are then imported in to mysql by a process that I dont have access to. They basicly hacked a large text field in table-b to fake a one-to-many relationship with table-a. Since I dont own the flat file or the database I am trying to work with what i got. If designing from scratch I certainly wouldn't do it like this. – personalt Oct 04 '11 at 04:39

1 Answers1

1

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 |
+--------------+------------+
vietean
  • 2,975
  • 9
  • 40
  • 65
  • This works perfectly.. I was looking at FIND_IN_SET in the [MYSQL Manual](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set) but once I saw it returning a the position of a string in a substring I didnt think it could solve my problem. Thanks.. – personalt Oct 04 '11 at 05:25
  • @personalt: Welcome you to SO. This is perfect site. You just write nice question and the answer will come. – vietean Oct 04 '11 at 08:02