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 |
One way of doing this is:
WITH cte AS (
SELECT ID,
hobby,
COUNT(hobby) OVER(PARTITION BY ID) AS cnt
FROM tab
GROUP BY ID,
hobby
)
SELECT t1.ID AS id1,
t2.ID AS id2
FROM cte t1
INNER JOIN cte t2
ON t1.ID < t2.ID
AND t1.hobby = t2.hobby
AND t1.cnt = t2.cnt
GROUP BY t1.ID, t2.ID, t1.cnt
HAVING COUNT(*) = t1.cnt
Output:
| id1 | id2 |
|---|---|
| 1 | 3 |
Check the demo here.
You can accomplish this by using GROUP_CONCAT to group ids by hobby and then splitting the concatenated pairs with SUBSTRING_INDEX:
This query will return the hobby with many members :
SELECT pairs
FROM (
select hobby, GROUP_CONCAT(DISTINCT ID) as pairs
from members
group by hobby
) as s
GROUP BY pairs
HAVING COUNT(pairs) > 1
Result :
pairs
1,3
The comma separated pairs will then be converted into columns in the final query :
WITH cte as (
SELECT pairs
FROM (
select hobby, GROUP_CONCAT(DISTINCT ID) as pairs
from members
group by hobby
) as s
GROUP BY pairs
HAVING COUNT(pairs) > 1
)
select SUBSTRING_INDEX(pairs, ',', 1) AS ID1,
SUBSTRING_INDEX(pairs, ',', -1) AS ID2
from cte
Result :
ID1 ID2
1 3
with data (id,hobby) as (
select 1, 'Tennis' union all
select 1, 'Football' union all
select 1, 'Football' union all
select 2, 'Cards' union all
select 2, 'Painting' union all
select 3, 'Tennis' union all
select 3, 'Football' union all
select 4, 'Cards' union all
select 5, 'Tennis' union all
select 5, 'Football' union all
select 5, 'Cards'
)
, udata(id,hobby) as (
select distinct id, hobby
from data
)
, cdata(id, n) as (
select id, count(distinct hobby) as n
from data
group by id
)
select id1, id2 from (
select u1.id as id1, u2.id as id2, count(*) as n,
c1.n as no1, c2.n as no2
from udata u1
join udata u2 on u2.id > u1.id and u1.hobby = u2.hobby
join cdata c1 on c1.id = u1.id
join cdata c2 on c2.id = u2.id
group by u1.id, u2.id
) t
where n = no1 and n = no2
;
(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 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.
with cte as (
select id, group_concat(distinct hobby order by hobby) hobbies
from members
group by id
)
select c1.id as id1, c2.id as id2, c1.hobbies
from cte c1
inner join cte c2 on c1.hobbies = c2.hobbies and c1.id < c2.id
Note that it is important to order the hobbies in the lists, so they can be consistently compared.
| id1 | id2 | hobbies |
|---|---|---|
| 1 | 3 | Football,Tennis |
