Join on piped column data

Question

I have to work with a MySQL database where some columns have different IDs saved in one column, separated by a piped character. For example:

Users table:

id | username | groups
1  | user1    | 1|2|3|5

The group IDs correspond to groups. It shows in what groups a user is placed in. So there is also a Groups table:

Groups table:

id | groupname
1  | group1
2  | group2
3  | group3
4  | group4
5  | group5

What i'd like to do is select a user and then select all the groupnames that this user is placed in, something like a JOIN. For user1 this is all groups except group4.

This would obviously be easier if the groups were saved in another table called user_groups, which could look like this:

user_groups table:

fk_user_id | fk_group_id
1          | 1
1          | 2
1          | 3
1          | 5

But, unfortunately i cannot change the database schema since an entire system is already based in this.

I'd like to know if there is a smart way of getting all the groupnames when selecting a user in a single query. For example a result could look something like this:

id | username | groups  | groupnames
1  | user1    | 1|2|3|5 | group1|group2|group3|group5

Is it possible somehow to do something like this in one query with MySQL?

maybe half of your problem can be done like this:http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field — , May 13 '13 at 15:44

score 2 · Accepted Answer · answered May 13 '13 at 15:44

In MySQL, you can use find_in_set() for this type of join:

select *
from user u join
     groups g
     on find_in_set(g.groupname, u.groups) > 0;

In MySQL, or other databases, you can use like as well:

select *
from user u join
     groups g
     on concat('|', g.groupname, '|') like concat('%|', u.groups, '|%');

Join on piped column data

1 Answers1