group mysql count values in comma separated field -

i have users table columns: user_id, mechanic_id

and

mechanics table id

i count how many users have same mechanic.

users table

+-------------------------+ | user_id   mechanic_id   | +-------------------------+ | 1           1,2         | | 2           2,1         | | 3           2,1,8,16    | | 4           1,16,3      | +-------------------------+ 

mechanics table

+------+ | id   | +------+ | 1    | | 2    | | 3    |   ... 

count $id1 is: 4

count $id2 is: 3

count $id3 is: 1

count $id8 is: 1

count $id16 is: 2

best solution: scrap table design , rebuild normalized once. simple join + group + count query work.

worst solution: use mysql's find_in_set() function:

select mechanics.id, count(user_id) mechanics left join users on (find_in_set(mechanics.id, users.mechanic_id) > 0) group mechanics.id