I'm using MySQL. I have 2 tables say items and item_inv.
Here's how items table looks like:
------------------------------------------
item_id | item_name | item_code
------------------------------------------
1 | red shirt | code1#code2#code3
------------------------------------------
2 | blu shirt | code1#code2#code3
------------------------------------------
3 | my shirt | code1#code2#code3
------------------------------------------

and item_inv looks like:
----------------
code | quantity
----------------

Note that item_code inside items table has values of the form
code1#code2#code3 and in this case item_inv table will have values such as this:
----------------
code | quantity
----------------
code1 | 10
code2 | 11
code3 | 99
----------------

Here's what i want to do: I want to display to the user data in the following format:
-----------------------------------
item_id | item_name | code
-----------------------------------

Is there a SELECT statement that would do the above? Please advise. I'm aware of the standard SELECT statement that could be used like the follwing:
SELECT items.item_id, items.item_name, item_inv.code FROM items, item_inv WHERE (items.item_code=item_inv.code);
The above won't work obviously since item_code is a mixture of several codes.
Please advise.

The best solution would be to change your table structure. If you have a many to many relationship, that should be held in another table rather than in a list stored as a string in a database field.

...assuming you can't change the table structure, would this work(?):
...try using the REPLACE function to change the '#' separated values to comma separated values and use an 'IN' clause to see if the value is in the list? Unfortunately, I can't try this out, but I feel like something like that might work? Or maybe the MAKE_SET function? There has to be something in the string functions, right?

(sorry if I'm distracted by the Daily Show )