CODEWITHSUNDEEP
sql
InformaticsStudent
InformaticsStudent

Reputation: 1

Finding exact matches within table

Lets say we have a table with two columns, name and likes. Each name may have multiple likes.

I need a query to find the names that have the exact same likes as name=Bob (not fewer and contained in Bob, and not more including all of Bob's likes.)

My idea is for each name, join their likes to Bob's likes and check if it matches Bob's likes still, while also checking that each have the same number of likes. Any help?

Upvotes: 0

Views: 73

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

One method is to concatenate the variables into a single string and compare them. Using MySQL syntax:

select t.name
from (select name, group_concat(likes order by likes) as likes
      from t
      group by name
     ) t cross join
     (select group_concat(likes order by likes) as likes
      from t
      where name = 'Bob'
     ) bob
     on t.likes = bob.likes and t.name <> 'Bob';

Another method that will work in almost any other database is to use a join and counts:

select t.name
from (select t.*, count(*) over (partition by name) as numlikes
      from t
      where name <> 'Bob'
     ) t cross join
     (select t.*, count(*) over (partition by name) as numlikes
      from t
      where name = 'Bob'
    ) bob
    on t.likes = bob.likes and t.cnt = bob.cnt
group by t.name, t.cnt
having count(*) = t.cnt;  -- the number of matches is the number of likes

Upvotes: 0

Aman Sura
Aman Sura

Reputation: 256

You need a self join here. That means join a table to itself as shown below - 

Select distinct a.name, b.name from table as a join      table as b on a.likes = b.likes and a.name != b.name

Note the use of distinct clause and inequality of names to avoid a name matching with itself

This will output rows as-

a.name  |  b.name
----------------------------
Bob         |  John
Smith      |  Ram

If you want to check for a specific user as Bob, then you can append a where clause to the query.

Upvotes: 1

Related Questions