Select rows that have at least one set member

Question

I am having a problem with my sql command.
So I have a column of type SET let's call it test_set, and I have multiple values that a row can have, let's say test1, test2

And let's say I have one row that has test1,
and another that has test1 and test2,

How could I select all rows that have test1, (should return both rows)
What about all rows that have test2 (Should return the second row)

As of right now, I know you can do SELECT * WHERE test_set='test1'
But this only returns rows that only have test1, not the ones that have test1 and test2.

Thanks!

Your question is quite unclear. Can you post sample data in tabular form? — Felix Pamittan, May 06 '16 at 00:17

score 1 · Accepted Answer · answered May 06 '16 at 00:24

1

If I am understanding you correctly you have a VARCHAR column containing comma delimited values.

In that case a LIKE will work for you.

SELECT * WHERE test_set LIKE '%test1%'

You might want to consider changing the database schema if you can though - For example have a separate "SETS" table that references your original table.

Ex.

CREATE TABLE MY_DATA (ID INT NOT NULL, NAME VARCHAR(255) NULL)
CREATE TABLE SETS (ID INT NOT NULL, MY_DATA_ID INT NOT NULL, SET_ITEM VARCHAR(50) NOT NULL)

SELECT * 
FROM MY_DATA D
     JOIN SETS S
          ON S.MY_DATA_ID = D.ID
WHERE S.SET_ITEM = 'test1'

answered May 06 '16 at 00:24

William Gates

311
3
10

Not sure what you are saying, so gonna give a bit more explanation as to what I want. I have this data: https://gyazo.com/79ca2cece15f00413b3e9e3e0888af81 As you can see the first column is a SET of text, I want to be able to do a select of all rows that have Party, but also do a select of all rows that contain Rockin Out – Pandawan May 06 '16 at 01:48
1

You should be able to do that using the above select statement, just add an OR test_set LIKE '%Rocking Out%' As to my second statement - The table doesn't appear to be normalized. You want want to have columns where values you want to search on are separated by commas. Instead you should create additional tables. – William Gates May 06 '16 at 02:44
Ok thanks, the LIKE worked. Though I don't quite understand what you mean by normalizing, I just started MySQL/SQL so I don't know everything about it. Also, would creating a second table be better? What would make it more interesting to use it rather than a SET? – Pandawan May 06 '16 at 04:04
Checking out https://en.wikipedia.org/wiki/Database_normalization – William Gates May 06 '16 at 12:05

Select rows that have at least one set member

1 Answers1