CODEWITHSUNDEEP
mysqlsql
L.P.
L.P.

Reputation: 169

SQL Comment Grouping

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?

Upvotes: 2

Views: 150

Answers (2)

Iłya Bursov
Iłya Bursov

Reputation: 24146

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

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562260

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

Upvotes: 4

Related Questions