SQL Comment Grouping

Question

I have two table in MySQL

Table 1: List of ID's

--Just a single column list of ID's

Table 2: Groups

--Group Titles

--Members **

Now the member field is basically a comments field where all the ID's that are part of that group are listed. So for instance one whole field of members looks like this:

"ID003|ID004|ID005|ID006|ID007|ID008|... Etc."

There they can be up to 500+ listed in the field.

What I would like to do is to run a query and find out which ID's appear in only three or less groups.

I've been taking cracks at it, but honestly I'm totally lost. Any ideas?

What @Mihai is sarcastically pointing out is that you have a fundamental DB design issue. You really need to normalized that data. Are you open for suggestions on changing your DB schema, because without that, I would recommend just trying to solve this problem in your programming language of choice. — Mike Brant, Oct 21 '13 at 21:26

score 4 · Accepted Answer · edited May 23 '17 at 10:31

4

Edit; I misunderstood the question the first time, so I'm changing my answer.

SELECT l.id
FROM List_of_ids AS l
JOIN Groups AS g ON CONCAT('|', g.members, '|') LIKE CONCAT('%|', l.id, '|%')
GROUP BY l.id
HAVING COUNT(*) <= 3

This is bound to perform very poorly, because it forces a table-scan of both tables. If you have 500 id's and 500 groups, it must run 250000 comparisons.

You should really consider if storing a symbol-separated list is the right way to do this. See my answer to Is storing a delimited list in a database column really that bad?

The proper way to design such a relationship is to create a third table that maps id's to groups:

CREATE TABLE GroupsIds (
  memberid INT,
  groupid INT,
  PRIMARY KEY (memberid, groupid)
);

With this table, it would be much more efficient by using an index for the join:

SELECT l.id
FROM List_of_ids AS l
JOIN GroupsIds AS gi ON gi.memberid = l.id
GROUP BY l.id
HAVING COUNT(*) <= 3

edited May 23 '17 at 10:31

Community

1
1

answered Oct 21 '13 at 21:24

Bill Karwin

538,548
86
673
828

Can you elaborate on how that helps on getting which ID's appear in only three or less groups? – Filipe Silva Oct 21 '13 at 21:32
@FilipeSilva The difference in length between the string with separators adn the string without shows the number of ids.Separators=number of groups,separator being 1 character. – Mihai Oct 21 '13 at 21:33
1

this query solves a little bit different problem, it shows groups with 3 or less members, but OP needs to find members with 3 or less groups – Iłya Bursov Oct 21 '13 at 21:33
@Mihai I see. That is indeed a clever trick to get how many ids are in there, but i'm not sure that addresses OP's problem. – Filipe Silva Oct 21 '13 at 21:35
@FilipeSilva, you're right, I misunderstood the OP's question, so I have written a new answer. – Bill Karwin Oct 21 '13 at 21:39
@BillKarwin. Yes. i saw that. Looks more on point now. But i still liked the trick you had before :) – Filipe Silva Oct 21 '13 at 21:41
Excellent Mr. Karwin. Worked just as labeled, thank you! Sadly the DB design is out of my hands, all I can do is make suggests to improve it and work with what I have. – L.P. Oct 22 '13 at 19:14
That's a common situation, alas. – Bill Karwin Oct 22 '13 at 19:20

Iłya Bursov · Answer 2 · 2013-10-22T19:32:16.890

2

select * from
(
    select ID,
    (
        select count(*)
        From Groups
        where LOCATE(concat('ID', a.id, '|'), concat(Members, '|'))>0
    ) as groupcount
    from ListIDTable as a
) as q
where groupcount <= 3

edited Oct 22 '13 at 19:32

answered Oct 21 '13 at 21:27

Iłya Bursov

23,342
4
33
57

SQL Comment Grouping

2 Answers2