-1

I have a table in my DB that looks like so:

member_id    name    mgroup_others    member_group_id
    1        Name1      2,3,10              1
    2        Name2      4,5,10              1
    3        Name3      6,7,10              1
    4        Name4       8,9                1

I need to create a SELECT statement that selects all members WHERE a specified integer is in the 'mgroup_others' comma demented list. So for example, I would write a SQL statement that would select all members where 'mgroup_others' contains '10' and it would return member 1, 2 and 3 but not 4 since 10 is not in that field. Any help would be greatly appreciated! Thanks. I am writing this in PHP as well.

Mihai
  • 26,325
  • 7
  • 66
  • 81
Jon Erickson
  • 1,876
  • 4
  • 30
  • 73
  • [`FIND_IN_SET()`](http://www.tutorialspoint.com/mysql/mysql-string-functions.htm#function_find-in-set) may prove to be useful. – Funk Forty Niner Jun 13 '14 at 23:11
  • 2
    Lame way: use [FIND_IN_SET](http://stackoverflow.com/questions/4155873/find-in-set-vs-in) (this approach [will *not* use indices](http://stackoverflow.com/a/23945341/2864740)), Cool way: normalize the database. – user2864740 Jun 13 '14 at 23:11
  • 1
    I cannot normalize the DB as it is apart of a large CMS that was built that way. – Jon Erickson Jun 13 '14 at 23:12
  • I don't know why @Mihai deleted the answer because it looks correct to me. – Gordon Linoff Jun 13 '14 at 23:13
  • I'm having trouble understanding whether using FIND_IN_SET is "lame" or not; from said comment. – Funk Forty Niner Jun 13 '14 at 23:14
  • http://stackoverflow.com/search?q=FIND_IN_SET%28%29 – GolezTrol Jun 13 '14 at 23:18
  • 1
    @Fred-ii- `FIND_IN_SET` is basically a string operation. You could see it as a special version of `LIKE`. Therefor using `FIND_IN_SET` bypasses the use of indexes on that field. A normalized version will probably be faster and a bit more portable. Disadvantage is that you need an extra table, but if you're building a serious application, you will probably have multiple tables anyway, and normalizing this should hardly be an obstacle. So `FIND_IN_SET` is lame because it makes it so tempting to not normalize your database properly. ;) – GolezTrol Jun 13 '14 at 23:21
  • @GolezTrol Thanks. I am aware of the normalization of databases but haven't adventured myself far enough into it. Your explanation helped me to understand the difference between using `FIND_IN_SET()` and `LIKE` a bit more, in relation to the question. – Funk Forty Niner Jun 13 '14 at 23:37
  • 1
    @Fred-ii- You're welcome. :) Maybe you can read [this pretty short Wikipedia chapter](http://en.wikipedia.org/wiki/Database_normalization#Normal_forms). It lists the various normal forms. Now maybe you don't want to learn and use all of them yet, but making fields 'atomic' (not containing multiple values in a single field) is actually the first normal form, and therefor the first step in normalization. And it is needed to make proper foreign key constraints and indexes. It is the first and probably the most important step in keeping your database performant when the amount of data grows. – GolezTrol Jun 13 '14 at 23:54
  • @GolezTrol That link makes for very good and interesting reading, thank you. I've read 1/2 of it already and will keep reading it, whilst saving a local copy for future reference; *cheers.* – Funk Forty Niner Jun 14 '14 at 00:00

3 Answers3

5
SELECT * FROM table WHERE FIND_IN_SET(10,mgoup_others)>0

This is a temp solution,in a production db this will be slower than a turbo snail.

Mihai
  • 26,325
  • 7
  • 66
  • 81
-1

Since mgroup_others is a varchar column what if you simply use LIKE operator other than FIND_IN_SET() like

select * from table1
where mgroup_others like '%10'

You can as well use FIND_IN_SET() like below

select * from table1
where find_in_set(10,mgroup_others)

See a demo fiddle here http://sqlfiddle.com/#!2/034711/2

Rahul
  • 76,197
  • 13
  • 71
  • 125
  • this only works if there are no other groups ending in `10` i.e. `110` – FuzzyTree Jun 13 '14 at 23:24
  • That would match `1,2,1000` as well. If you would use `like`, you should have something like `WHERE CONCAT(',', mgroup_others, ',') LIKE '%,10,%'` or something like that. Not very convenient compared to the `FIND_IN_SET` function, which is already pretty work-aroundish in itself. – GolezTrol Jun 13 '14 at 23:26
  • @FuzzyTree, that's correct. `find_in_set()` would be much proper but didn't included that in answer since it's already answered by Mihai but see the fiddle included that's gives both way. – Rahul Jun 13 '14 at 23:26
  • @GolezTrol, see the fiddle included. I have mentioned the query with `find_in_set()` as well. didn't included it since it was already mentioned in Mihai's answer. Anyways, edited my answer. – Rahul Jun 13 '14 at 23:27
  • 1
    I see, but your answer said you can avoid `find_in_set` in favor of `like`. Not only would that give incorrect results (especially if you want to find items matching '3' without having a second `%`). So now you have a wrong answer and a fiddle that doesn't demonstrate your answer but Mihai's. – GolezTrol Jun 13 '14 at 23:30
  • @GolezTrol, you interpreted wrong. I mean to say another option other than `find_in_set` – Rahul Jun 13 '14 at 23:31
-1

Use LIKE

SELECT * 
FROM yourtable 
WHERE mgroup_others LIKE '%10%'
Nawed Khan
  • 4,393
  • 1
  • 10
  • 22
  • That would match `1,2,1000` as well. If you would use `like`, you should have something like `WHERE CONCAT(',', mgroup_others, ',') LIKE '%,10,%'` or something like that. Not very convenient compared to the `FIND_IN_SET` function, which is already pretty work-aroundish in itself. – GolezTrol Jun 13 '14 at 23:24