0

I have a MySQL DB table as follows

MyTable
+------+------+------------------+
| id   | name |  items           |
+------+------+------------------+
|    1 | John |item1,item2,item3 |
|    2 | Ram  |item1,item2       |
+------+------+------------------+

Is there any way possible to use the value from items in an IN clause? I know it treats the entire thing as 1 literal string, but is there anyway it would treat it as separate items, almost as if I manually inserted those items in as a raw query?

eric MC
  • 766
  • 2
  • 10
  • 36
  • in pure SQL, that kind of value mapping is not really possible. if you combine it with a language that can allow for that kind of mapping (PHP for example), then yeah, totally. – PlantTheIdea Jun 12 '14 at 19:29
  • yeah thats the next step - just move on and manually insert the items w. PHP – eric MC Jun 12 '14 at 19:30
  • 3
    Put your database in [first normal form](http://en.wikipedia.org/wiki/First_normal_form) by creating an ID, Item table, inserting each item as a separate record, and remove MyTable.items. – Dour High Arch Jun 12 '14 at 19:32
  • +! @DourHighArch - i didnt want to go there necessarily, but this is likely poor architecture. storage of multiple values in a single field is a big no-no, standards-wise. – PlantTheIdea Jun 12 '14 at 19:36
  • standards wise yes, definitely bad practice, just trying to hammer out a one off site that will be offline w/in the week - just curious if it was possible, have never heard anything about it. – eric MC Jun 12 '14 at 19:38
  • I was writing up an answer but the question got closed. You can go to http://stackoverflow.com/a/17942691/1504882 for a solution that I think would work – Elias Jun 12 '14 at 19:41

1 Answers1

-1

  1. make a query that returns numbers to the maximum number of elements that you may have

    N
--
1
2
..

  2. write a query to extract i-th element from a list using SUBSTR and INSTR

  3. join 1st query with 2nd query putting N as i-th element, then filter out null values

vav
  • 4,584
  • 2
  • 19
  • 39