search a SET column?
I have a table with two columns, the first column is of type VARCHAR and the second of type SET.
person3 group1, group2
I want to retrieve everyone in "group1"...
Which returns only person1 and not person1 and person3 as I want.
SELECT * FROM mytable WHERE column2 = 'group1';
I have searched through the manual and found several ways to search for data matching several criteria (ANY, IN, SOME, ALL) but not "the other way around".
I guess what I'm really asking is how do I search within a column of type SET?
I think that you can use FIND_IN_SET to do this.
SELECT * FROM mytable WHERE FIND_IN_SET('group1', column2)
Just as a side note, SET and FIND_IN_SET are only supported on MySQL so if you are planning on porting this to another database any time then I would consider using something else.
Lilleman: Thank you, this worked.
Hartmann: I did consider that SET is not considered by all to be "the right way" to do things; I did not know however that MySQL is the only database to support SET. Unfortunately with this fix the rest of my program/script now works wonderfully and I have already invested too much time to change everything. I will now hope that my script/database can serve it's purpose long enough that I do not need to make these changes. ;)
Some helpful links I found in solving this issue:
interesting that they are both by the same guy!
(coincidence? i don't think so ;))