Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    1

    Unanswered: Special SELECT statement with field parsing

    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.

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    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 )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •