0

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.

monkeyman
  • 135
  • 1
  • 4
  • 14
  • 1
    oh no, csv data in a column, please see Junction, Intersect or association tables [here](http://stackoverflow.com/a/32620163) or elsewhere – Drew Jul 07 '16 at 00:06
  • 1
    The real solution is to redesign your table – juergen d Jul 07 '16 at 00:08
  • Thanks for the link @Drew. I was unaware of the right way to go about this. I'll look into rebuilding the tables as a long term solution. However due to time constraints I'd still like to find a query that'll help me with what I have now. – monkeyman Jul 07 '16 at 00:18

3 Answers3

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

If possible, you should change your database schema. In general, it's not good to store comma delimited lists in a relational database.

However, if that isn't an option, try it like this with a join:

select g.GroupID 
from Groups g 
    join UserRecord ur on ';' || g.groupmembers like '%;' || ur.mqid || ';%'
where md5(ur.UID) = ' \*md5 checksum*\ '
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • oh lordie a crutch :p ... let's heal monkeyman and make him a sprinter. – Drew Jul 07 '16 at 00:10
  • @Drew -- if possible the table should be changed, but I've dealt with lots of 3rd party systems where that wasn't an option. I agree though, ouch... – sgeddes Jul 07 '16 at 00:11
  • I appreciate your help guys. The long term solution will be to redesign my tables (I was unaware of the correct procedures for this type of thing). However due to time constraints I'd still like to find a query to use that'll work with what I've got. @sgeddes Your query is unfortunately returning the same results as mine, which is to list all the GroupID's. Any other thoughts? – monkeyman Jul 07 '16 at 00:17
  • He will figure it out for ya. Often people make sure that it starts and ends each chunk with a delimiter like `|` or whatever (even at the beginning and far tail). Makes for easier and fail-safe `like` searches (albeit at snails pace) – Drew Jul 07 '16 at 00:19
0

I do think the database should actually be restructured but that has been said.

In your case I believe the following comes as closest to your query that will solve your problem I believe:

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

Your statement checks whether or not an ID exists somewhere in the string, so if you are looking for ID 2 you will also find groups that have members like 12, 23, 2222 etc. With this modification you will only find groups that have ;ID; or start with ID; so that only complete ID matches will be found.

Ron Deijkers
  • 2,791
  • 2
  • 22
  • 28