0

I have a comma delimited list like this saved in a mysql table:

-----------------
|id | list      |
-----------------
| 4 |12,45,65,4 |

Each number in "list" corresponds with an ID in another table.

Is there a way so I can query the other table based on theses IDs and bring up those rows associated with the numbers in "list"?

adamzwakk
  • 709
  • 2
  • 11
  • 23
  • possible duplicate of [Mysql WHERE problem with comma-separated list](http://stackoverflow.com/questions/3946831/mysql-where-problem-with-comma-separated-list) – Michael Berkowski May 22 '12 at 20:02

1 Answers1

2

Not any efficient way with your current schema. The correct and most efficient way to do it is to change the schema to hold multiple rows like this:

-----------------
|id | list      |
-----------------
| 4 |    12     |
| 4 |    45     |
| 4 |    65     |
| 4 |     4     |

Then you use JOIN operations to connect 4 to every related row in your other table.

This is called database normalization and is a very important topic in database design. Relational database systems are built to handle just this types of problems in an efficient manner.

Emil Vikström
  • 90,431
  • 16
  • 141
  • 175
  • I thought so, looks like I'll have to restructure things slightly. Thanks for the input! – adamzwakk May 22 '12 at 20:01
  • 1
    @adamzwakk: Read this, too: **[Is storing a comma separated list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad)** – ypercubeᵀᴹ May 22 '12 at 20:02