monkeyman monkeyman - 1 year ago 67
MySQL Question

SQL query with LIKE subquery

I have a table called

as shown below.

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

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

I have another table called
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
but made no progress with them.

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download