SQL query with LIKE subquery

Question

I have a table called Groups as shown below.

+---------+--------------------------------+
| GroupID | GroupMembers                   |
+---------+--------------------------------+
|   1     | 2342342;234234;234235;3533453; |
+---------+--------------------------------+
|   2     | 345345345;345345353;345335334; |
+---------+--------------------------------+

The GroupMembers field is a list of UserID's separated by semi-colons.

I have another table called UserRecord that stores the UserID's with other details.

I need an SQL query that will take the md5 checksum of a UserID and find which group it is in.

I have tried the following statement, which only returned a list of all GroupID's, instead of just the group that the user is in.

SELECT g.GroupID FROM Groups g WHERE g.GroupMembers LIKE '%' || (SELECT UID FROM UserRecord ur WHERE md5(ur.UID) = ' \*md5 checksum*\ ') || '%'

I've also fiddled with INNER JOIN and WHERE EXISTS but made no progress with them.

Answer 1

I strongly, strongly, strongly agree that the right solution is a GroupMembers table with one row per group and member in that group. I also live in the real world so know that we are sometimes stuck with other people's bad design decisions (or, perhaps, the decision decision is good . . . for another purpose).

The correct syntax in MySQL to express this is:

select g.GroupID 
from Groups g join
     UserRecord ur
     on concat(';', g.groupmembers) like concat('%;',  ur.mqid, ';%')
where md5(ur.UID) = ' \*md5 checksum*\ ';

By default, in MySQL, the || operator is a logical OR. It just tests that the adjoining values are or are not 0.

You can also express the on condition as:

     on find_in_set(ur.mqid, replace(g.groupmember, ';', ',')) > 0;