1

Possible Duplicate:
Is storing a comma separated list in a database column really that bad?

I have a mysql db column of comma separated text:

  id  keys
 ------------
  1  Key1, Key2
  2  key3
  3  key2, key3, key4
  4  key5, key2

Question 1

I need results like this:

id    keys
----------
1    Key1
2    Key2
3    key3
4    key4
5    key5

Question 2

Also I need a second query to match the above condition. eg.

select all the rows with key2

Output should be

id
---
1
4

NOTE: Only in a single query, no stored procedures, no functions.

I have seen similar questions but with not the desired answer.

NOTE : Thanks for suggesting that data should not be stored in a comma seprated field. But I have not developed the system and database. I am just about to do a samll upgradation.

Community
  • 1
  • 1
GajendraSinghParihar
  • 8,829
  • 11
  • 33
  • 63
  • 2
    why do you not want to use a function or stored procedure? – Taryn Nov 02 '12 at 11:58
  • any string function and sub-query allowed? – Saifuddin Sarker Nov 02 '12 at 12:02
  • 1
    Why don't you store the data properly (ie: how you want the results to be in question 1). – podiluska Nov 02 '12 at 12:03
  • @Mishu Sarker yes. but i don't think this require sub-query – GajendraSinghParihar Nov 02 '12 at 12:04
  • @podiluska because I am not the person who developed this system and database. and thanks for the downvote – GajendraSinghParihar Nov 02 '12 at 12:05
  • 3
    Read this please: **[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)** And the short answer is: **Yes, it's really that bad.** – ypercubeᵀᴹ Nov 02 '12 at 12:05
  • -1 to @bluefeet, that does not solve the issue – ajreal Nov 02 '12 at 12:08
  • @ypercube Thanks for suggesting that data should not be stored in a comma seprated field. But I have not developed the system and database. I am just about to do a samll upgradation. – GajendraSinghParihar Nov 02 '12 at 12:08
  • 2
    @ajreal I know it doesn't solve the issue, but limiting the tools that are allowed to be used makes this pretty much pointless. This could be solved by using a function. – Taryn Nov 02 '12 at 12:09
  • 1
    And now you know that even a simple query is worse than hard with such an awful design. Now, if you can't afford to change the structure (which should really be the first choice), please post more details. What are these "keys"? Integers, strings, what sizes, etc? What other (related) tables do you have? Sample data, a few rows from each table would be great. – ypercubeᵀᴹ Nov 02 '12 at 12:10
  • @ypercube `key1` its a string and every row has a primary key as id what else do you need the sample data is same as i provided in the question. – GajendraSinghParihar Nov 02 '12 at 12:13
  • @champ the best way is to redesign this schema regardless how small part of your role in this system – ajreal Nov 02 '12 at 12:15
  • @ajreal no i will do it without redesign the database. please wait for my answers – GajendraSinghParihar Nov 02 '12 at 12:17
  • @Champ: So which one of the following sample would be closer to yours?: `key1, key7, key3` or `Adam, George, Bill` or `"Adam", "George", "Bill"` or `Adam Jones, George Clooney, Bill Clinton` – ypercubeᵀᴹ Nov 02 '12 at 12:18
  • And what is the maximum number of keys in one cell? – ypercubeᵀᴹ Nov 02 '12 at 12:22
  • @ypercube `Adam Jones, George Clooney, Bill Clinton` and maximum number of keys in one cell are not much .. but can be any... – GajendraSinghParihar Nov 02 '12 at 12:25
  • 2
    @champ So what is your religious objection to functions? – podiluska Nov 02 '12 at 12:34

1 Answers1

3

Here's a temporary solution, until you redesign your tables. It will not perform very well, due to the CROSS JOIN. It also needs creating a new "Number" table:

CREATE TABLE Number
  ( i INT PRIMARY KEY );

Fill it with numbers up to expected maximum number of keys in a cell:

INSERT INTO Number
  VALUES
    (1), (2), ..., (1000) ;

Then run this to give you the all distinct keys in the comma-separated column: 

SELECT DISTINCT 
    SUBSTRING_INDEX(
          SUBSTRING_INDEX( 
                t.`keys`
              , ', '
              , n.i ) 
        , ', ' 
        , -1 ) AS `key`
FROM 
    Number AS n
  CROSS JOIN 
    YourTable AS t ;

Tested at: SQL-Fiddle

ypercubeᵀᴹ
  • 109,746
  • 18
  • 170
  • 231