mysql - How to normalize comma separated values within column of table and then run query

Question

Let's say I have a table with the following structure:

 | column1    |    column2       |
 |------------|------------------|
 | a          |    1,L,3,K,5,    |
 | b          |    R,6,7,8,9     |
 | c          |    8,9,10,D      |
 | d          |    1,2,3,H       |

Let's say that column1 can potentially continue on through z and that column2 can continue on with random numbers and letters. I would like a general solution that can apply to any number of rows and columns, and number of values in column2.

I want to run a query in MySQL that will search all the values in column2 and output the letters in column1 that contain a 3 in column2. The output should be:

 | column1    |
 |------------|
 | a          | 
 | d          |  

Many posts have answers with queries that directly accomplish this, but I would like to do it the correct way. I am new to sql, but I believe that would mean to normalize the data in column2 by creating a new table and running a query on this new table instead.

Can someone help me with the code to normalize and run this query in MySQL? Thanks.

Answer 1

To normalize this table, you would want to use a table consisting of two columns, with the primary key being both columns. It would look something like this:

| column1    |    column2       |
|------------|------------------|
| a          |    1             |
| a          |    3             |
| a          |    L             |
| b          |    R             |
| c          |    8             |
| d          |    3             |

Then you can use this simple query:

Select column1 from table where column2 = 3;