I have the following table
members:
ID | hobby |
---|---|
1 | Football |
1 | Tennis |
1 | Football |
2 | Cards |
2 | Painting |
3 | Tennis |
3 | Football |
4 | Cards |
and i want to select pairs of members only if they have the exact same hobbies (without duplicates).
So in the table above, i want the query to output:
id1 | id2 |
---|---|
1 | 3 |
my query:
SELECT m1.id as id1 , m2.id as id2
FROM members m1 inner join members m2
ON m1.id < m2.id
WHERE m1.hobby in (
SELECT distinct(m2.hobby)
)
GROUP BY id1,id2
but i get:
id1 | id2 |
---|---|
1 | 3 |
2 | 4 |
4
Answers
You can accomplish this by using
GROUP_CONCAT
to group ids by hobby and then splitting the concatenated pairs withSUBSTRING_INDEX
:This query will return the hobby with many members :
Result :
The comma separated pairs will then be converted into columns in the final query :
Result :
Demo here
(you could add count(distinct hobby) over(partition by id) as n in udata and add the condition on n in the JOIN between u1 and u2 later but MySQL doesn’t support yet count distinct over partition…)
One way of doing this is:
Output:
Check the demo here.
One simple method uses string aggregation. The idea is to build a list of all hobbies of each member; we can then self-join the result to generate pairs of users that share the exact same list.
Note that it is important to order the hobbies in the lists, so they can be consistently compared.
fiddle