Question 168423 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL Query: Select pairs of members sharing exact values?

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
Tags:

4

Answers


  1. 0

    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

    Demo here

    Login or Signup to reply.
  2. 0 
    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…)

    Login or Signup to reply.
  3. 0

    One way of doing this is:

    • counting how many unique hobbies each ID has
    • self-joining to catch matching hobbies and number of hobbies together, on different ids
    • ensure the count of hobbies is equal to the count of matching records for each id
    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.

    Login or Signup to reply.
  4. 0

    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

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search